This is one of those data type conversions which I
occasionally have to do in DataStage, but can never seem to remember. So, I
thought I would write this quick post to document the data type conversion code, which is
really easy, once I finally remember how to do it again.
The TimestampFromDateTime Function
I use the TimestampFromDateTime function to do this data type conversion. I’m sure there are other ways to achieve the result, but I find this method clean and easy to perform. The TimestampFromDateTimefunction is in the Functions > Date & Time menu.
To populate the function, you need only add your date field on
use ’00:00:00’ as your time element
TimestampFromDateTime Function Format
TimestampFromDateTime with Time Element example
TimestampFromDateTime(<<Date Field Here>>, ’00:00:00′)
Over the years have occasionally use the action column feature, however, the last month or so I have found myself using it quite a lot. This is especially true in relation to the tea set and not just in relation to the change capture stage.
The first thing you need to know is, if you want to prevent getting the ‘no action column found’ notice on the target stage, need to ensure that the action column has been coded to be a single character field char (1). Otherwise, the Netezza connector stage will not recognize your field as an action column.
While most developers will commonly work with the action column feature in relation to the change capture stage, it can also be very useful if you have created a field from one or more inputs to tell you what behavior the row requires. I have found that this approach can be very useful and efficient under the right circumstances.
Action column configuration example
Change Code Values Mapping To Action Column
Here’s a quick reference table to provide the interpretation of the change type code to the actual one character action column value to which it will need to be interpreted.
Change Code Type
Change Type Code
Action Column Value
Copy (Data Without Changes)
value for this Change Type
Example Transformer Stage, Derivation
Here is a quick transformer stage derivation coding example to take advantage of the action call capabilities. If you haven’t already handled the removal of the copy rows, you may also want to add a constraint.
The combination I most frequently find myself using is the insert and update combination.
if Lnk_Out_To_Tfm.change_code=1 then ‘I’
Else if Lnk_Out_To_Tfm.change_code=2 then ‘D’
Else if Lnk_Out_To_Tfm.change_code=3 then ‘U’
Home > InfoSphere Information Server 11.7.0 > InfoSphere DataStage and QualityStage > Developing parallel jobs > Introduction to InfoSphere DataStage Balanced Optimization > Job design considerations > Specific considerations for the Netezza connector
During the course of the week, the discussion happened regarding the different places where a person might read the DataStage and QualityStage logs in InfoSphere. I hadn’t really thought about it, but here are a few places that come to mind:
IBM InfoSphere DataStage and QualityStage Operations Console
IBM InfoSphere DataStage and QualityStageDirector client
IBM InfoSphere DataStage and QualityStageDesigner client by pressing Ctrl+L
I found working with date literal, when working with the Infosphere SFDC Connector soql, to be counterintuitive for me. At least as I, normally, as I use SQL. I spent a little time running trials in Workbench, before I finally locked on to the ‘where clause’ criteria data pattern. So, here a quick example.
SOQL DATE String Literals Where Clause Rules
Basically, the date pattern is straight forward. The basic rules are for a soql where clause:
No Casting function, or casting for the where soql where clause to read.
Example SOQL DATE String Literals
So, here are a couple of date string literal examples in SQL:
Example SQL with Date String Literal Where Clause
From Target_and_Segmentation__c t
where t.Target_Date__c > 2014-10-31
Salesforce Developer Documentation
Home, Developer Documentation, Force.com SOQL and SOSL Reference
In its simplest form, the convert function in Infosphere DataStage is a string replacement operation. Convert can be used to replace a specific character, a list of characters, or a unicode character (e.g. thumbs Up Sign or Grinning Face).
convert(‘<<Value to be replaced’,'<<Replacement value >>’,<<Input field>>)
Using the Convert Function to remove a list of Characters
Special Characters in DataStage Handles/converts special characters in a transformer stage, which can cause issues in XML processing and certain databases.