You can define derived columns or custom column mappings for one or more tables by importing the contents of a text file containing JSON or tab or semicolon-delimited directives. The file dialog will (by default) show files with an extension of .json, .txt, or .tsv.
JSON format is supported in SQDR 5.22 & later and permits more complex SQL expressions than the original text format and allows embedding of special characters such as tab and semicolon. It also adds the new column attribute staging_expression for the map and column actions. JSON directives can be intermixed with the original tab or semicolon-delmited text format.
JSON lines are enclosed in brackets and must contain a string element called action which must have a value of one of the following
drop: removes a source column from the subscription
map: redefines a source column in the subscription. It may just change the destination characteristics (i.e. the destination column name) or combine a "drop" and "column" operation in one line - rendering a source column using a derived column expression (i.e. source_expression and stage_expression)
column: adds a derived column to the subscription
pcolumn: defines a column as a "partition" rule column. The value must be a character literal. For more information about using a partition rule column, see the technical document SQDR: Partition Key for Many-to-One Replication.
Required parameters depend on the action specified.
schema string: may use wildcard for suffix
table string: may use wild card for suffix
source_column string: The source column name for drop, map
source_expression string: expression for column action. Also may be specified for map if redefining the existing source_column name to a derived column expression; otherwise this should be an empty string.
stage_expression string: expression for column action. Also may be specified for map if redefining the existing source_column name to a derived column expression; otherwise this should be an empty string.
dest_column string
dest_type string
precision number
scale number
nullable bool
comment string: optional string element for documentation
Drop a column named No_Column (in all tables being added):
{ "action": "drop", "schema": "%", "table": "%", "source_column": "No_Column", "comment": "drops a column called "No_Column - if it is present" }
Change the data type of the destination column:
{ "action": "map", "schema": "%","table": "%", "source_column": "ID", "dest_column":"ID", "source_expression":"", "stage_expression":"","dest_type":"bigint", "precision":19, "scale":0, "nullable":false, "comment": "mapped - just changes dest characteristics" }
Change the contents of column DESCRIP to lower case in table STARTRAK.APPS:
{"action": "map", "schema": "STARTRAK","table": "APPS", "source_column": "DESCRIP", "dest_column":"DESCRIP", "source_expression":"lower(\"DESCRIP\")", "stage_expression":"", "dest_type":"nvarchar", "precision":50, "scale":0, "nullable":false, "comment": "mapped to use derived column logic" }
Add a derived column named LastUpdated of data type datetime2, containing the timestamp of when the table was baselined OR when the row was last updated on the source system:
{"action": "column", "schema": "STARTRAK","table": "APPS", "dest_column":"LastUpdated", "source_expression":"@ts", "stage_expression":"","dest_type":"datetime2", "precision":27, "scale":7, "nullable":false, "comment": "column" }
Add a partition rule column named PartitionColumn, containing the literal value 1:
{"action": "pcolumn", "schema": "STARTRAK","table": "APPS", "dest_column":"PartitionColumn", "source_expression":"'1'","dest_type":"char", "precision":1, "comment": "pcolumn - never nullable" }
Additional examples may be found in the topic Defining Derived Columns.
Note: This format may be retired in future versions of SQDR.
Derived Columns:
<schema> <table> COLUMN <expression> <dest_column> <dest_type> <precision> <scale> <nullable>
where <expression> is the value for a derived column.
You may use the wild card character % in the <schema> or <table> field to indicate all schemas or all tables; for instance, specifying % % will add the derived column to all subscriptions currently being created.
A derived column definition can be used to override the default column name and data type of the "RRN" column (@RRN for Db2 for i source or @ROWID for Oracle source) used by SQDR; see examples below.
Derived Column as a Partition Key:
<schema> <table> PCOLUMN <expression> <dest_column> <dest_type> <precision> <scale> FALSE
Specify the PCOLUMN keyword when the derived column is to be used as a Partition Key - i.e. it will be used as part of a composite primary key for many-to-one incremental replications. The expression must be a static value, and the nullable field must be FALSE. See the technical document Using a Partition Key for Many-to-One Replication for details.
Custom Column Mappings:
<schema> <table> MAP <source_column> <dest_column> <dest_type> <precision> <scale> <nullable>
where <source_column>is the name of source column that will be mapped to <destColName> using the specified type, precision, scale & nullable values.
Custom Column Mappings:
<schema><table> MAP <source_column>[:[@]<source_expression>] <dest_column> <dest_type> <precision> <scale> <nullable>
Specifying the source_column token with a "colon" character followed by the source derivation value will replace the source column in the subscription with the derived column phrasing (if the source column exists in the source table).
If the derived column expression begins with the special character @, then a simple macro expansion is performed on the source_expression and dest_column replacing any occurrence of ^s with the matched source column name. This is of particular use if wild card matching is being performed on the schema or filename.
If : <source_expression> is omitted, then the normal "MAP" function is performed, changing only the destination characteristics.
If the @ macro designation is omitted, then no substitution of the source expression or destination column name will occur.
Example:
MYSCHEMA MYTABLE MAP DESCRIP:@UPPER(^s) ^sTransformed varchar 50 0 TRUE
Replace the column named DESCRIP with the derived column phrasing, creating a column DESCRIPTransformed varchar(50) NULLABLE
Omit a Column:
<schema> <table> DROP <source_column>
The DROP command will omit a source column from the subscription.
You can also specify the equivalent of a DROP action by using the MAP directive and specifying an empty destination column name; see example below.
Examples:
MYSCHEMA MYTABLE COLUMN MDY(MDYZ) MDYZ_1 date 10 0 TRUE
%;%;COLUMN;@RRN;RelativeRecordNumber;bigint;19;0;FALSE
This creates a derived column named "RelativeRecordNumber" (instead of "RRN") of type of bigint (instead of decimal(15,0)).
MYSCHEMA MYTABLE MAP COLSRC COLDEST date 10 0 TRUE
MYSCHEMA MYTABLE DROP COLSRC
MYSCHEMA;MYTABLE;MAP;COLSRC;;date;10;0;TRUE (this is equivalent to DROP)
The same input table may be used to import a list of table names in the Source dialog, specifying TABLE in the third field instead of COLUMN, MAP or DROP. See Importing and Exporting Source Objects.
Begin a line with the hyphen character to designate a comment.
If a script has been imported on the Destination page, selecting the Rebuild button on the Column page will utilize the script.