PARAMETERSET Values substitution issue in ODBC/DB2 stages

[G.K.K]

Hi,

I have a parallel job with below PARAMETERSET .
>Parameterset name :PS_DB_CONN
Parameters in the above paramterset are:
1. DB_DSN
2. DB_USERID
3. DB_PSWD
>DATASET_PATH=C:/USR/datasets/

I have below job design with the above parameter set and job parameter:

DB2 API Stage ---->Targetdataset

For the source stage (DB2) connection details are passed like below:
ServerName=#PS_DB_CONN.DB_DSN#
UserId=#PS_DB_CONN.DB_USERID#
Password=#PS_DB_CONN.DB_DB_PSWD#

The target datset name is passed with dataset path as a parameter.

>When I execute the job it is aborting and in the log it is saying that '#PS_DB_CONN.DB_DSN#' the value is not found .And i can see that the original values(TEST_DSN,TEST_USER,password) that is provided in the parameters of parameterset is not substituting.

And when i click the view data in the database stage, it is saying that please pass the values for ServerName,UserId,Password even after passing the parameterset for these.

But when i hard code the connection details with original values with out passing through parameters/parameterset. The jobs is executing successfully.

I tried in the ODBC connector and DB2 API stages with parameterset and parameters. The job is not substituting the parameterset/parameter values for database stages and job aborts.
I hard coded the actual values in the database stages in another job for db connection and the job executed successfully.

So, Can some please let me know what might be the issue with the database stage for not substituting the parameter/parameterset values in the job.

Thanks in Advance,
G.K.K

[chulett]

For completeness, can you tell us what the exact version you are running?

[ray.wurlod]

Too many DBs in "=#PS_DB_CONN.DB_DB_PSWD#".

[G.K.K]

For completeness, can you tell us what the exact version you are running?

I am using Version 8.0.1.

[G.K.K]

Too many DBs in "=#PS_DB_CONN.DB_DB_PSWD#". ...

It was typo error here. But actual parameter in the job is for the parmeterset is DB_PSWD

[ray.wurlod]

Please post the "job starting" event from the job log from an occasion when the job was invoked from a sequence. This event includes all parameter values received by the job.

[G.K.K]

Please find the job log below for the parameter(#DATABASE.SERVER_NAME#) that was unable to substitute value.please check the Event ID=56:
Item #: 46
Event ID: 45
Timestamp:: 2013-06-24 12:04:31
Type: Control
User Name: TRY-CC1F48C4|TRY
Message:: Starting Job pj_Db2_Read.
DATABASE = DATABASE
DATABASE.SCHEMA_NAME = TRY (From value file)
DATABASE.SERVER_NAME = DB2_DEV (From value file)
DATABASE.USER_ID = db2admin (From value file)
DATABASE.PASSWORD = ******** (From value file)
DIRECTORYPATHS = Development
DIRECTORYPATHS.SRC_DRTY = F:\Datastage_Work_Files\Try\SourceFiles\ (From value file)
DIRECTORYPATHS.TGT_DRTY = F:\Datastage_Work_Files\Try\TargetFiles\ (From value file)
DIRECTORYPATHS.SRC_SCHEMA_FL_DRTY = F:\Datastage_Work_Files\Try\SchemaFiles\Src\ (From value file)
DIRECTORYPATHS.TGT_SCHEMA_FL_DRTY = F:\Datastage_Work_Files\Try\SchemaFiles\Tgt\ (From value file)
DIRECTORYPATHS.SRC_DATASET_PATH = F:\Datastage_Work_Files\Try\Datasets\ (From value file)
DIRECTORYPATHS.TGT_DATASET_PATH = F:\Datastage_Work_Files\Try\Datasets\ (From value file)

Item #: 47
Event ID: 46
Timestamp:: 2013-06-24 12:04:32
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: Environment variable settings:
ALLUSERSPROFILE=C:\Documents and Settings\All Users
APT_COMPILEOPT=-W/TP -W/EHa -DAPT_USE_ANSI_IOSTREAMS -c -W/Zc:wchar_t-
APT_COMPILER=cxx
APT_CONFIG_FILE=C:\IBM\InformationServer\Server\Configurations\4node.apt
APT_ERROR_CONFIGURATION=severity, !vseverity, !jobid, moduleid, errorIndex, timestamp, !ipaddr, !nodeplayer, !nodename, opid, message
APT_LINKER=cxx
APT_LINKOPT=-s -W/dll -W/base:0x50000000 -W/Zc:wchar_t-
APT_MONITOR_MINTIME=10
APT_OPERATOR_REGISTRY_PATH=C:\IBM\InformationServer\Server\Projects\Try\buildop
APT_ORCHHOME=C:/IBM/InformationServer/Server/PXEngine
APT_USE_CRLF=1
BELL=^G
COMPUTERNAME=TRY-CC1F48C4
CommonProgramFiles=C:\Program Files\Common Files
ComSpec=C:\WINDOWS\system32\cmd.exe
DB2INSTANCE=DB2
DB2PATH=C:\IBM\SQLLIB
DISPLAY=:0.0
DS_ENABLE_RESERVED_CHAR_CONVERT=0
DS_OPERATOR_BUILDOP_DIR=buildop
DS_OPERATOR_WRAPPED_DIR=wrapped
DS_TDM_PIPE_OPEN_TIMEOUT=720
DS_TDM_TRACE_SUBROUTINE_CALLS=0
DS_USERNO=-2572
DSIPC_OPEN_TIMEOUT=30
FLAVOR=-1
FP_NO_HOST_CHECK=NO
INCLUDE=C:\IBM\SQLLIB\INCLUDE
LIB=C:\IBM\SQLLIB\LIB
NUMBER_OF_PROCESSORS=2
NUTCROOT=C:\PROGRA~1\MKSTOO~1
OS=Windows_NT
OSH_STDOUT_MSG=1
PATH=<abunchofstuff>
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.sh;.ksh;.csh;.sed;.awk;.pl
PIDTOK=1248
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 6 Model 15 Stepping 13, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=0f0d
ProgramFiles=C:\Program Files
PWD=C:\IBM\InformationServer\Server\Projects\Try
PX_DBCONNECTHOME=C:/IBM/InformationServer/Server/DSComponents
ROOTDIR=C:/PROGRA~1/MKSTOO~1
SHELL=C:/PROGRA~1/MKSTOO~1/mksnt/sh.exe
SystemDrive=C:
SystemRoot=C:\WINDOWS
TEMP=C:\WINDOWS\TEMP
TERM=console
TERMCAP=C:\PROGRA~1\MKSTOO~1\etc\termcap
TERMINFO=C:\PROGRA~1\MKSTOO~1\usr\lib\terminfo
TMP=C:\WINDOWS\TEMP
UNIVERSE_CONTROLLING_TERM=1
UNIVERSE_PARENT_PROCESS=1248
USER=TRY-CC1F48C4\TRY
USERPROFILE=C:\Documents and Settings\db2admin
VS80COMNTOOLS=C:\Program Files\Microsoft Visual Studio 8\Common7\Tools\
WHO=Try
windir=C:\WINDOWS

Item #: 48
Event ID: 47
Timestamp:: 2013-06-24 12:04:32
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: Parallel job initiated

Item #: 49
Event ID: 48
Timestamp:: 2013-06-24 12:04:32
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: Parallel job default NLS map ASCL_MS1252, default locale OFF

Item #: 50
Event ID: 49
Timestamp:: 2013-06-24 12:04:34
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: main_program: IBM WebSphere DataStage Enterprise Edition 8.0.1.4458
Copyright (c) 2001, 2005-2007 IBM Corporation. All rights reserved

Item #: 51
Event ID: 50
Timestamp:: 2013-06-24 12:04:35
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: main_program: orchgeneral: loaded
orchsort: loaded
orchstats: loaded

Item #: 52
Event ID: 51
Timestamp:: 2013-06-24 12:04:36
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: main_program: Requesting delayed metadata.

Item #: 53
Event ID: 52
Timestamp:: 2013-06-24 12:04:42
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: main_program: APT configuration file: C:/IBM/InformationServer/Server/Configurations/4node.apt
{
node "node1"
{
fastname "TRY-CC1F48C4"
pools ""
resource disk "C:/IBM/InformationServer/Server/Datasets" {pools ""}
resource scratchdisk "C:/IBM/InformationServer/Server/Scratch" {pools ""}
}
node "node2"
{
fastname "TRY-CC1F48C4"
pools ""
resource disk "C:/IBM/InformationServer/Server/Datasets" {pools ""}
resource scratchdisk "C:/IBM/InformationServer/Server/Scratch" {pools ""}
}
node "node3"
{
fastname "TRY-CC1F48C4"
pools ""
resource disk "C:/IBM/InformationServer/Server/Datasets" {pools ""}
resource scratchdisk "C:/IBM/InformationServer/Server/Scratch" {pools ""}
}

node "node4"
{
fastname "TRY-CC1F48C4"
pools ""
resource disk "C:/IBM/InformationServer/Server/Datasets" {pools ""}
resource scratchdisk "C:/IBM/InformationServer/Server/Scratch" {pools ""}
}

}

Item #: 54
Event ID: 53
Timestamp:: 2013-06-24 12:04:42
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: db2_customer_sel,0: Info: pj_Db2_Read.db2_customer_sel: Using NLS map ASCL_MS1252

Item #: 55
Event ID: 54
Timestamp:: 2013-06-24 12:04:42
Type: Fatal
User Name: TRY-CC1F48C4|TRY
Message:: db2_customer_sel,0: Failure during execution of operator logic.

Item #: 56
Event ID: 55
Timestamp:: 2013-06-24 12:04:42
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: db2_customer_sel,0: Output 0 produced 0 records.

Item #: 57
Event ID: 56
Timestamp:: 2013-06-24 12:04:42
Type: Fatal
User Name: TRY-CC1F48C4|TRY
Message:: db2_customer_sel,0: Fatal Error: Fatal: [IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009
DSTAGE-DB2CLI-0005`:`Unable to connect to DB2 server '#DATABASE.SERVER_NAME#'.

Item #: 58
Event ID: 57
Timestamp:: 2013-06-24 12:04:42
Type: Fatal
User Name: TRY-CC1F48C4|TRY
Message:: node_node1: Player 1 terminated unexpectedly.

Item #: 59
Event ID: 58
Timestamp:: 2013-06-24 12:04:47
Type: Fatal
User Name: TRY-CC1F48C4|TRY
Message:: main_program: APT_PMsectionLeader(1, node1), player 1 - Unexpected exit status 1.

Item #: 60
Event ID: 59
Timestamp:: 2013-06-24 12:04:47
Type: Fatal
User Name: TRY-CC1F48C4|TRY
Message:: main_program: Step execution finished with status = FAILED.

Item #: 61
Event ID: 60
Timestamp:: 2013-06-24 12:04:47
Type: Info
User Name: TRY-CC1F48C4|TRY
Message:: main_program: Startup time, 0:12; production run time, 0:00.

Item #: 62
Event ID: 61
Timestamp:: 2013-06-24 12:04:47
Type: Control
User Name: TRY-CC1F48C4|TRY
Message:: Job pj_Db2_Read aborted.C:/IBM/InformationServer/Server/Datasets

[crystal_pup]

By any chance, have you included the parameter name in the API stage within single quotes? Also, can you hardcode the server name and let the user and password be parameterised, then try running the job and verify if the error is generated on the other parameters or not.

[G.K.K]

What ever the parameter or parameterset provided to the db stage, it is thowing the error that i pasted in the above replies. but other stages like dataset stage passing parameters or parameterset is working and substituting proper values

[chulett]

Then I would suggest you involve your official support provider, perhaps there is a patch available.