First parallel job

JPalatianos · Post by **JPalatianos** » Wed Apr 15, 2009 1:10 pm

Hi,
We are developing our first parallel job and early on already hit a stumbling block. In our server version of the job we defined the following administrative variables and imported them in the job prperties.
$MF_DSN
$MF_UID
$MF_Password

and just coded the following in the ODBC stage:
#$MF_DSN#
#$MF_UID#
#$MF_Password#

In the parallel job ODBC I can't get this to work....I tried with and without the # character with no luck. I can only get it to work if I hard code the connection parameters in the ODBC.
Thanks - - John

throbinson · Post by **throbinson** » Wed Apr 15, 2009 1:41 pm

Do a search of DSCAPIOP

I haven't worked with 8 but this could be the same problem(s) as I and others experienced with EE jobs not correctly substituting the environment variables for values at runtime.

JPalatianos · Post by **JPalatianos** » Wed Apr 15, 2009 2:33 pm

I have noticed that the variables look to be substitued properly (at least the dsn and UID)

Server:NJROS1BBLA0602
Project:CRM
Job No:2
Job name:Test2
Invocation:
Event Number:0
Event type:Control
User:NJROS1BBLA0602\DSADM
Timestamp:4/15/2009 4:29:20 PM
Message Id:DSTAGE_RUN_I_0070
Message:
Starting Job Test2.
$MF_UID = Z8TDSTG [$PROJDEF]
$MF_Password = ********
$MF_DSN = DBB5 [$PROJDEF]

JPalatianos · Post by **JPalatianos** » Wed Apr 15, 2009 2:35 pm

The fatal error I receive when I try executing is:

ODBC_Connector_0: [IIS-CONN-ODBC-000004] ODBC function "SQLConnect" reported: SQLSTATE = 08001: Native Error Code = -30,020: Msg = [IBM][CLI Driver] SQL30020N Execution of the command or SQL statement failed because of a syntax error in the communication data stream that will affect the successful execution of subsequent commands and SQL statements: Reason Code "0x124C"("0209")"". SQLSTATE=58009
[IIS-CONN-ODBC-000004] ODBC function "SQLConnect" reported: SQLSTATE = IM006: Native Error Code = 0: Msg = [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

lstsaur · Post by **lstsaur** » Wed Apr 15, 2009 3:20 pm

John,
Make sure your $MF_Password parameter is keyed in as $PROJDEF.

JPalatianos · Post by **JPalatianos** » Wed Apr 15, 2009 5:15 pm

I did check to make sure all were defined as $PROJDEF.....
The weird thing is when i check the Job Status Details in Director they are identical for the run with the hard coded parameters(succesful and I can connect from the ODBC stage)
Current job parameters:
$MF_UID=Z8TDSTG
$MF_Password=L<;@AIV8>=3M4O7M4;JLBK0G
$MF_DSN=DBB5

with those when i use the admin variables(job fails and I cannot connect from the ODBC stage)
Current job parameters:
$MF_UID=Z8TDSTG
$MF_Password=L<;@AIV8>=3M4O7M4;JLBK0G
$MF_DSN=DBB5

throbinson · Post by **throbinson** » Thu Apr 16, 2009 5:31 am

The DSCAPIOP problem manifests itself at the stage level. Meaning the job itself and most stages will substitute correctly. I think it would be worth checking to make sure the ODBC stage is correctly substituting as well. To do this I think you need to set the Project Reporting variable OSH_DUMP to true and re-run. Then look at the dump in Director and verify that the ODBC stage is using substituted values for the env vars and NOT, for example, DSCAPIOP_MF_UID

JPalatianos · Post by **JPalatianos** » Thu Apr 16, 2009 5:53 am

Thanks for the suggestion, when I look at the OSH_DUMP I see.....
type=\'string\'>1.0</DescriptorVersion><PartitionType type=\'int\'>-1</PartitionType></Common><Connection><DataSource modified=\'1\' type=\'string\'><![CDATA[$MF_DSN]]></DataSource><Username modified=\'1\' type=\'string\'><![CDATA[$MF_UID]]></Username><Password modified=\'1\' type=\'string\'><![CDATA[$MF_Password]]></Password>

Is this where I should be seeing the substitued values for teh DSN,UID and PWD?

throbinson · Post by **throbinson** » Thu Apr 16, 2009 5:56 am

I believe so. DSCAPIOP is not your problem.

JPalatianos · Post by **JPalatianos** » Thu Apr 16, 2009 6:31 am

OK, I rebuilt the job from scratch and am using the following for the ODBC parameters:
#$MF_DSN#
#$MF_UID#
#$MF_Password#

all defined as $PROJDEF in the job properties. Now the job runs clean, but I am still unable to connect via the ODBC stage in designer(tried both the test and view data buttons)

JPalatianos · Post by **JPalatianos** » Thu Apr 16, 2009 1:00 pm

I just opened a PMR with IBM......will post any information I receive to resolve this issue.

JPalatianos · Post by **JPalatianos** » Thu Apr 16, 2009 1:05 pm

I just opened a PMR with IBM......will post any information I receive to resolve this issue.

JPalatianos · Post by **JPalatianos** » Thu Apr 30, 2009 12:23 pm

IBM tried a bunch of things and nothing worked. We had an IBM DS consultant working on site and I ran it by him......he didn't recall why but remembers that the ODBC Enterprise stage should be used in parallel jobs and not ODBC Connector stage. All work fine with the Enterprise stage.

sjfearnside · Post by **sjfearnside** » Thu Apr 30, 2009 2:10 pm

I had a similiar problem with the ODBC connector on AIX. My support provider requested I apply a patch. The patch was patch_JR32595v2_server_aix_801. You can enquire from your support provider to see if there is a similiar patch for your OS. We never applied the patch since we are in the planning process of moving to V8.1 and do not want to disrupt the current production environment.