IBM Support

Limitation of the Parameter APT_OSL_PARAM_ESC_SQUOTE on Plugins on Parallel Canvas.

Technote (troubleshooting)


Problem(Abstract)

The APT_OSL_PARAM_ESC_SQUOTE parameter was added to indicate that an escape character should be placed in front of any single quotes found in job parameters so that the single quote would be passed through to the underlying components. If the APT_OSL_PARAM_ESC_SQUOTE parameter is set then for the parameter colname='adam', the single quotes are escaped and sent through the underlying components as \'adam\'. Otherwise, just the string adam is sent.

On Parallel Canvas if the job-parameters having single-quoted strings are used with APT_OSL_PARAM_ESC_SQUOTE, there are limitations when the same parameter is used with operators/connectors and plugins.

Symptom

Case -1

Consider a job with plugin stage (E.g. DB2API) and transformer (operator) stages and with job parameter TIMESTAMPVAL with single-quoted string having space within.
For example, TIMESTAMPVAL='2011-10-21 04:43:52.154081'.
If transformer stage uses this job parameter then the following error occurs:
Transformer_32: Unrecognized argument: 04:43:52.154081 [argvcheck/argvcheck.C:3779]

Since '2011-10-21 04:43:52.154081' is not escaped, the string that is passed to transformer stage is 2011-10-21 04:43:52.154081.
This happens because there is a space within the timestamp and the transformer sees these as two arguments and results in an Unrecognized argument error for the part after space.

If the APT_OSL_PARAM_ESC_SQUOTE parameter is set, then the transformer stages work well but fails in plugin stage with the following errors:

DB2_UDB_API_0: When processing argument -outlinkprops When processing output link property list: {
PREFETCH=50,
USERSQL='SELECT c1,c2 FROM test24726 where c2 < \2011\-10\-21 is not a valid property list: Expected "," or "}", got: <eof>
Encountered <eof> while seeking end of proplist

Case -2
Consider a job with job parameter WHERECond with value
WHERECond =WHERE ETL_TS = '2012-02-02 00:00:00.000000'

With the transformer stage's column output as "select * from xmeta.mytab":WHERECond, and if the column output is consumed by plugin stage for executing this SQL, as seen in case-1 the transformer stage sees the Unrecognized argument for space separated argument.

The following error occurs -
TX_BuildSQL: Unrecognized argument: 00:00:00.000000

If APT_OSL_PARAM_ESC_SQUOTE is set still error will be seen and following error is thrown
TX_BuildSQL: Unrecognized argument: 00:00:00.000000\

Cause

The Environment Variable APT_OSL_PARAM_ESC_SQUOTE introduces a problem for the Plugins, Operators, and Connectors because the escape sequence introduced by the Environment Variable APT_OSL_PARAM_ESC_SQUOTE is propagated to the down-stream stages and the removal of the Escape sequence is taken care by the respective Stages (be it Plugins, Operators or Connectors). As of today, no component had handled the escape sequence introduced by the Environment Variable.


Case-1, For SQL "SELECT c1,c2 FROM test24726 where c2 < '2011-10-21 04:43:52.154081'", if '2011-10-21 04:43:52.154081' needs to be parameterized, then Parameter should be TIMESTAMPVAL='2011-10-21 04:43:52.154081'. If APT_OSL_PARAM_ESC_SQUOTE is set then parallel framework introduces escapes that disturbs the specification of Plugin's single quotes and results in an error.

Case-2, APT_OSL_PARAM_ESC_SQUOTE will not help as timestamp is a substring in WHERECond=WHERE ETL_TS = '2012-02-02 00:00:00.000000'.


Environment

Issue Found in IS8.5.0.1 on AIX.

Diagnosing the problem

1)

The problem can be identified when any Plugin is used in combination with Transformer and/or Connector and/or Operator and the following error is observed:
Transformer_32: Unrecognized argument: 04:43:52.154081 [argvcheck/argvcheck.C:3779]

The error is followed by the below error:

DB2_UDB_API_0: When processing argument -outlinkprops When processing output link property list: {
PREFETCH=50,
USERSQL='SELECT c1,c2 FROM test24726 where c2 < \2011\-10\-21 is not a valid property list: Expected "," or "}", got: <eof>
Encountered <eof> while seeking end of proplist

This problem is related to Case 1 mentioned in this document.


2)
The following scenario also helps you identify the problem.
Consider a job with job parameter WHERECond with value
WHERECond =WHERE ETL_TS = '2012-02-02 00:00:00.000000'
With transformer stage's column output as "select * from xmeta.mytab":WHERECond

And the following warning error occurs:
TX_BuildSQL: Unrecognized argument: 00:00:00.000000

This problem is related to Case 2 mentioned in this document.


Resolving the problem

For Case 1:
1) The environment variable APT_OSL_PARAM_ESC_SQUOTE needs to be $UNSET.
2) Define separate Parameters with same value for the Connectors/Transformers and Plugins as Connector/Transformers have the parameters that are manually escaped and plugins have the parameters without escaping the strings.

For example,
Set APT_OSL_PARAM_ESC_SQUOTE to $UNSET.
Define two parameters TIMESTAMPVAL for plugins and TIMESTAMPVAL_CC for Connectors and/or Transformer.
If 2011-10-21 04:43:52.154081 is the value of the parameter, then set TIMESTAMPVAL to '2011-10-21 04:43:52.154081'and TIMESTAMPVAL_CC should to be set to \'2011-10-21 04:43:52.154081\'.

For Case 2:
Set APT_OSL_PARAM_ESC_SQUOTE to $UNSET.
Define parameter WHEREPARAM= WHERE ETL_TS= and TIMESTAMPVAL=\'2011-10-21 04:43:52.154081\' and change SQL to "select * from xmeta.mytab":WHEREPARAM:TIMESTAMPVAL
(or)
Set APT_OSL_PARAM_ESC_SQUOTE to $UNSET
Set Column to "select * from tablenam" : WHERECond

Make WHERECond a StageVariable in transformer stage with its value as

If TEST= 1 Then "WHERE ETL_TS=": TIMEST Else ""

Add TIMEST and TEST parameters in Job properties

and have TIMEST set to \'2012-02-02 00:00:00.000000\'

and set TEST to 1 or 0 depending on whether the 'where' clause is required or not.


Document information

More support for: InfoSphere DataStage

Software version: 8.5.0.1

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1578409

Modified date: 22 May 2012