Skip to main content

OutputOracleTransformer

Overview​

Transforms DataStage Oracle OUTPUT stages to SQL passthrough SELECT statements. Output stages are treated as data selection only (no DML operations generated).

Stage Type: OUTPUT (pxbridge operator with OracleConnectorPX) Output: SQL SELECT passthrough

Important Note​

OUTPUT stages do NOT generate INSERT/UPDATE/DELETE SQL. The converter only handles data transformation (SELECT statements). Actual data loading must be configured separately in Snowflake.

Capabilities​

  • Passthrough all columns from input (SELECT *)
  • Passthrough specific columns only
  • Preserves input schema
  • Documents target table metadata

DataStage Stage Example​

Input (.dsx format)​

#### STAGE: OutputTargetTable
## Operator
pxbridge
## Operator options
-Orientation link
-XMLProperties '<?xml version=\'1.0\' encoding=\'UTF-16\'?>
<Properties version=\'1.1\'>
  <Connection>
    <Server>[&"instance"]</Server>
    <Username>[&"username"]</Username>
  </Connection>
  <Usage>
    <TableName>SCHEMA.TARGET_TABLE</TableName>
    <BeforeSQL>DELETE SCHEMA.TARGET_TABLE WHERE ...</BeforeSQL>
  </Usage>
</Properties>'
-connector '{name=OracleConnector}'
-target 0 '{
      DSSchema=\'record (
        EVENT_DATE:nullable timestamp;
        SCENARIO_CODE:nullable string[max=10];
        VERSION_CODE:nullable string[max=50];
        SOURCE_INFO:nullable string[max=100];
        LOAD_DATE:nullable timestamp;
      )\'
}'

## General options
[ident('OutputTargetTable'); jobmon_ident('OutputTargetTable')]
## Inputs
0< [] 'ProcessedData:output.v'

Generated SQL Output​

SELECT
  *
FROM processeddata_V0S18

Output Schema​

[
  {"name": "EVENT_DATE", "type": "timestamp"},
  {"name": "SCENARIO_CODE", "type": "string"},
  {"name": "VERSION_CODE", "type": "string"},
  {"name": "SOURCE_INFO", "type": "string"},
  {"name": "LOAD_DATE", "type": "timestamp"}
]

How It Works​

1. Identify Output Stage​

Recognizes Oracle Connector output stages:

## Operator
pxbridge
-connector '{name=OracleConnector}'

2. Extract Metadata​

Documents (but does not convert):

  • TableName: Target table name
  • BeforeSQL: Pre-load SQL statements
  • AfterSQL: Post-load SQL statements

3. Generate Passthrough SELECT​

Creates SELECT from upstream stage:

  • With columns defined: SELECT with explicit column list
  • Without columns: SELECT * (full passthrough)
SELECT
  <columns or *>
FROM upstream_stage

BeforeSQL and AfterSQL​

DataStage OUTPUT stages can define SQL to run before/after data loading. These are documented as metadata but NOT converted to SQL. You must recreate them manually in Snowflake using stored procedures or orchestration.

Limitations​

  • ⚠️ No DML generation - INSERT/UPDATE/DELETE not generated by converter
  • ⚠️ BeforeSQL/AfterSQL not converted - Must be manually recreated in Snowflake
  • ModifyStageTransformer - For column filtering before output
  • TransformerStageTransformer - For column derivations before output
  • InputStageTransformer - Opposite operation (reads from Oracle)