We have a job where we are reading from a DB2 table with a user defined sql and writing in another DB2 table through one-one mapping in the transformer. The job is getting aborted with the following error :
1. Failure during execution of Operator Logic.
2. Fatal: [IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009
Unable to connect to DB2 server
The query has been tested in the database and it runs fine there.
pravin1581 wrote:
2. Fatal: [IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009
Unable to connect to DB2 server
The query has been tested in the database and it runs fine there.
Thanks in advance.
Hi,
Verify all the values to the properties in DB2 stage that throws the error. Mostly the server name, user id, password could be passed incorrectly.
Clearly, your error message is giving you the answer
Since, you could fire the sql on the database, but not through datastage, please check your DSNs, userid connectivity to DB2.
Clearly, your error message is giving you the answer
Since, you could fire the sql on the database, but not through datastage, please check your DSNs, userid connectivity to DB2.
Let us know the result.
But for the same query if we do a view data on the DB2 stage it returns the result, but when we run the job we get the error.
ArndW wrote:Are any of the DB2 connection fields (server,user,password) parameters?
Yes they were all env parameters and when we replace them with the actual values in all the individual stages , then the job runs fine, but we need to declare all the connection details through env parameters as per requirement and standard practice followed.
ArndW wrote:So you've narrowed it down to the runtime parameters. Without more information it is difficult to analyze what is going awry.
What else information can I share, even I am not able to fathom out the exact error, only way out is to declare the variables once again and then check the behaviour of the job.
pravin1581 : Yes they were all env parameters and when we replace them with the actual values in all the individual stages , then the job runs fine, but we need to declare all the connection details through env parameters as per requirement and standard practice followed.
pravin,
So,in simple terms if you are hardcoding the values (userid,pwd,host string)you could run the job properly. Then by passing the parameters your job is failing. so, by trail and error method check by hardcoding the parameters one by one.
Bhavesh.Shah:If you are using the DB2 API stage, there is patch available for this bug. Ask Ascential Support for the Patch.
pravin1581 : Yes they were all env parameters and when we replace them with the actual values in all the individual stages , then the job runs fine, but we need to declare all the connection details through env parameters as per requirement and standard practice followed.
pravin,
So,in simple terms if you are hardcoding the values (userid,pwd,host string)you could run the job properly. Then by passing the parameters your job is failing. so, by trail and error method check by hardcoding the parameters one by one.
Bhavesh.Shah:If you are using the DB2 API stage, there is patch available for this bug. Ask Ascential Support for the Patch.
I am just curious why this is a bug!!
I did the same but as soon as even a single parameter is incorporated the job fails.
Check that the appropriate quote characters have been used in SQL and that job parameter references are correctly encased in a pair of "#" characters wherever used in a passive stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ray.wurlod wrote:Check that the appropriate quote characters have been used in SQL and that job parameter references are correctly encased in a pair of "#" characters wherever used in a passive stage.
Only the connection details such as server name, user id and password has been parameterized , they dont appear in any sql.
Do you add them to the stage using the parameter helper tool (Insert Job Parameter)? What do they look like when loaded into properties? Is the generated connection string consistent with what you've put into the individual properties?
Are all the required "#" characters in place? You did not answer this following my earlier post.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.