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
I have found myself using this simple, but useful SQL time in recent weeks to research different issues and to help with impact analysis. So, I thought I would post it while I’m thinking about it. It just gives a list of views using a table, which can be handy to know. This SQL is simple and could be converted to an equi-join. I used the like statement mostly because I sometimes want to know if there are other views a similar nature in the same family (by naming convention) of tables.
Select All Fields From The _V_View
This is the simplest form of this SQL to views, which a table.
Select * from _v_view
where DEFINITION like ‘%<<TABLE_NAME>>%’ ;
Select Minimal Fields From The _V_View
This is the version of the SQL, which I normally use, to list the views, which use a table.
Occasionally, one runs into the problem of hidden field values breaking join criteria. I have had to clean up bad archive and conversion data with hidden characters serval times over the last couple of weeks, so, I thought I might as well capture this note for future use.
I tried the Replace command which is prevalent for Netezza answers to this issue on the web, but my client’s version does not support that command. So, I needed to use the Translate command instead to accomplish it. It took a couple of searches of the usual bad actors to find the character causing the issue, which on this day was chr(0). Here is a quick mockup of the command I used to solve this issue.
Example Select Statement
Here is a quick example select SQL to identify problem rows.
SELECT TRANSLATE(F.BLOGTYPE_CODE, CHR(0), ”) AS BLOGTYPE_CODE, BT.BLOG_TYP_ID, LENGTH(BT.BLOG_TYP_ID) AS LNGTH_BT, LENGTH(F.BLOGTYPE_CODE) AS LNGTH_ BLOGTYPE
FROM BLOGS_TBL F, BLOG_TYPES BT WHERE TRANSLATE(F.BLOGTYPE_CODE, CHR(0), ”) = BT.BLOG_TYP_ID AND LENGTH(BT.BLOG_TYP_ID) <>Length(LENGTH(F.BLOGTYPE_CODE) ;
Example Update Statement
Here is a quick shell update statement to remove the Char(0) characters from the problem field.
Update <<Your Table Name>> A
Set A.<<Your Field Name>> = TRANSLATE(A.<<Your FieldName>>, CHR(0), ”)
where length(A.<<Your Field Name>>) <> Length(A.<<Your FieldName>>) And << Additional criteria>>;
End of Support for IBM InfoSphere Information Server 9.1.0
IBM InfoSphere Information Server 9.1.0 will reach End of Support on 2018-09-30. If you are still on the InfoSphere Information Server (IIS) 9.1.0, I hope you have a plan to migrate to an 11-series version soon. InfoSphere Information Server (IIS) 11.7 would be worth considering if you don’t already own an 11-series license. InfoSphere Information Server (IIS) 11.7 will allow you to take advantage of the evolving thin client tools and other capabilities in the 2018 release pipeline without needing to perform another upgrade.
IBM Support, End of support notification: InfoSphere Information Server 9.1.0
I had a reason this week to perform a substring on a character in Netezza this week, something I have not had a need to do before. The process was not as straightforward as I would have thought, since the command is explained as a static position command, and the IBM documentation, honestly, wasn’t much help. Knowing full well, that text strings are variable having to provide a static position is not terribly useful in and of itself. So, we need to use an expression to make the substring command flexible and dynamic.
I did get it work the way I needed, but it took two commands to make it happen:
The First was the ’instr’ command to identify the field and character I wanted to substring on: instr(<<FIELD_NAME>>,’~’) as This provides the position number of the tilde (~).
The second was the ‘substr’ command in which I embedded the ‘instr’ command: substr(<<FIELD_NAME>>,0,instr(<<FIELD_NAME>>,’~’) )
This worked nicely for what I needed, which was to pick out a file name from the beginning of a string, which was delimited with a tilde (~)
Substring on a Character Command Format
This format example starts with position zero (0) as position 1 of substring command and goes to the first tilde (~) as position 2 of the substring command.
, instr(<>,’~’) as pos2
, substr(<<FIELD_NAME>>,0,instr(<<FIELD_NAME>>,’~’) ) as Results
IBM Knowledge Center, Home, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions
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