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
While investigating a recent Infosphere Information Server (IIS), Datastage, Essbase Connect error I found the explanations of the probable causes of the error not to be terribly meaningful. So, now that I have run our error to ground, I thought it might be nice to jot down a quick note of the potential cause of the ‘Client Commands are Currently Not Being Accepted’ error, which I gleaned from the process.
Error Message Id
An error occurred while processing the request on the server. The error information is 1051544 (message on contacting or from application:[<<DateTimeStamp>>]Local////3544/Error(1013204) Client Commands are Currently Not Being Accepted.
Possible Causes of The Error
This Error is a problem with access to the Essbase object or accessing the security within the Essbase Object. This can be a result of multiple issues, such as:
Object doesn’t exist – The Essbase object didn’t exist in the location specified,
Communications – the location is unavailable or cannot be reached,
Path Security – Security gets in the way to access the Essbase object location
Essbase Security – Security within the Essbase object does not support the user or filter being submitted. Also, the Essbase object security may be corrupted or incomplete.
Essbase Object Structure – the Essbase object was not properly structured to support the filter or the Essbase filter is malformed for the current structure.
IBM Knowledge Center, InfoSphere Information Server 11.7.0, Connecting to data sources, Enterprise applications, IBM InfoSphere Information Server Pack for Hyperion Essbase
While chasing an error to which only applied to join type stages, I thought it might be nice to identify what the InfoSphere Information Server DataStage / QualityStage are. There are three of them, as you can see from the picture above, which are the:
And, Merge Stage.
All three stages that join data based on the values of identified keycolumns.
IBM Knowledge Center, InfoSphere Information Server 11.7.0, InfoSphere DataStage and QualityStage, Developing parallel jobs, Processing Data, Lookup Stage
When you are controlling a chain of sequences in the job stream and taking advantage of reusable (multiple instances) jobs it is useful to be able to pass the Invocation ID from the master controlling sequence and have it passed down and assigned to the job run. This can easily be done with needing to manual enter the values in each of the sequences, by leveraging the DSJobInvocationId variable. For this to work:
The job must have ‘Allow Multiple Instance’ enabled
The Invocation Id must be provided in the Parent sequence must have the Invocation Name entered
The receiving child sequence will have the invocation variable entered
At runtime, a DataStage invocation id instance of the multi-instance job will generate with its own logs.
This approach allows for the reuse of job and the assignment of meaningful instance extension names, which are managed for a single point of entry in the object tree.