SRTransport can transform data as it moves from source to target. Data can be mapped between tables with different schemas. Data can be removed or added. Data can be qualified and validated. Data extracted from multiple sources can be loaded into the destination. One transport can use the batch version of SRTransport to run additional transports. 'Expressions' and 'Customizations' in SRTransport use the DataScript language that allows the user to make these modifications, you will need to become familiar with it. (For more detail see Using DataScript.)
Working with Expressions
The expressions that appear in the 'Value Expression' for a destination column are processed and passed into the destination tuple before it is sent to the database. Possibilities include, 'NULL' if the expression is left blank, string or numeric constants, values from the source tuple either as is or modified, function results, and results of calls outside SRTransport such as getdate()(which calls the system) or your own C routines.
Values that are available to expressions include:
- variables from the source columns
- variables declared in Transport Customization
- SR·Transport's variables
- results of math or string expressions
- results of functions
- constants
Mapping
Using SR·Transport shows how source columns can be mapped directly to destination columns. This page shows how to map data between tables with different schemas.
Consider the example below.
- Notice that columns don't need to line up horizontally.
- Notice that the two name columns in the source are concatenated to make one name column in the destination (with a space added between them).
- The monthlySalary from the source is multiplied by 12 to create the yearlySalary.
- A constant string is used for the employer column in the destination table.
- Finally, a function that is independent of the source data is used to supply the date.
Number variables can use math expressions.
monthlySalary * 12
Strings can be concatenated and manipulated in many ways.
upper( stringA + stringB + "more string" )
Functions or Procedures
There are many functions that can be used in expressions. The most important functions are available by selecting 'Functions' from the Transport menu. Select a destination column and then chose a function from the menu and it will appear in the 'Value Expression' for that column. (See the getdate() function in the example above.) Then fill in any necessary parameters. You can also write your own functions (see Writing Your Own Functions).
Customizing a Transport
The Transport Run Loop
When a transport is run, SRTRansport first prepares the source and the destination and then begins executing a run loop. Certain things happen in a specified order. See SRTransport Processing Architecture. Before any data is fetched from the source 'Run preprocessing' is called. Any 4gl in the 'Run preprocessing' window will be executed at this time. Then SRTransport starts processing rows. After each row is fetched the 'Qualifying Expression' and then the 'Validating Expression' are evaluated. Then Row preprocessing occurs. Data that is not rejected is sent to the destination while rejected rows are written to the .reject file. Then 'Row postprocessing' occurs. Finally after all the rows have been processed 'Run postprocessing' occurs.
The Transport Customization Pop-up Menu
The Transport Customization window has a pop-up menu with the following items. You can edit in the text area and when you click the check syntax button you'll get error messages if something is wrong.
Notes
It's a good idea to document your customizations. Anything entered here will be printed in a transport report. (See the Transport menu documentation.)
Qualifying Expression
A qualifying expression acts like a where clause. Normally when the source is a table in a database you would use the 'where' clause in the module window (it is much more efficient to let the database handle this). If however your source is a text file this allows you to qualify each row. Rows that fail to qualify are silently discarded.
For example:
customerID <= 1000
Only customers with IDs up to 1000 would be processed. Customers with IDs greater than 1000 will not be moved to the destination and there is no error condition and nothing is written to the log or reject file.
Validating Expression
Rows that fail the validating expression will be rejected. Here you can check that data in a file or database is valid before you insert it into its destination. Rejected rows are written to the .reject file and if 'Pause on error' is checked in the 'Transporter Room' window, the transport will pause.
For Example:
yearlySalary = monthlySalary * 12
Obviously if the statement isn't true there is some kind of error in the source table. You might want to know about this.
Run preprocessing
This is a good place to declare variables that you will use later in processing rows. You might need to use sys() to run other transports to load data to temporary tables. You might need to create the tables that you plan to load with data. Or delete old data from existing tables.
If you are writing your own 4gl functions this is a good place to put them.
Row preprocessing
Values assigned to variables in Row preprocessing can be used in the 'Value Expression' column. You can use execsql() to fetch data from other tables that you might need for the destination.
Row postprocessing
You can check to see if the row was rejected and take steps if necessary. The global variable row_transported (an integer) will be set to 1 if the row succeeded.
Variables from the source row are still available. This is also a good place to increment counters or calculate a next primary key etc.
Run postprocessing
Here is the place to delete temporary tables, delete data from source tables or run additional transports that are dependant on tables or files produced from this transport.
Make sure that you remove variables that you declared in 'Run preprocessing'.
Using execsql()
execsql() can be used send sql to the source or destination. You can use it to query other tables for data that might be needed for computations or in the destination table.
Warning ! - this function executes during trail runs
For instances if you have a 'customerID' in your source table but not a 'name' and you want to combine them in the destination you could write something like this.
In Run preprocessing:
declare name string, sql string
In Row preprcessing:
set sql = 'SELECT name FROM customerNames
WHERE customerID = "' + customerID + '"'
set name = execsql(sql)
You can now use 'name' in the 'Value Expression' of the transport and assign it to the destination.
In Run postprocessing remove the string variables.
remove name, sql
Using sys()
sys() can be used to call external code from SRTransport. That includes UNIX utilities, your own external routines or SR·Agent.
Warning ! - this function executes during trail runs
Simply create a string that looks exactly like what you would type on a command line and call sys(myCommandString). The command will execute and return any output in to a text variable.
Suppose you have some transport named "/home/transports/myTransport.transport". Further suppose that SRTransport is located in /home. SRTransportES will be located in (on OSX) /home/SRTransport.app/Contents/Resources/srtransport.
Now we can run that transport from the present transport. Perhaps in run postprocessing.
declare result text
set text = sys("srtransport_es t3.transport
-password joe DEST -password mike")