What is "parameter1" in ODBC DB2 wire protocol err

bobyon · Post by **bobyon** » Mon Jun 15, 2009 3:12 pm

Can someone help with the cause/resolution of the following error

[ODBC DB2 Wire Protocol driver]Value has not been specified for parameter 1.

I searched and found similar messages but not this particular error.

I assumed that parameter 1 referred to the first data value referenced by "?" in the following SQL

Code: Select all

INSERT INTO #$DB2DATABASE#.PROCESSCONTROL (PROCESSCON_ID,PROCESSCON_INST_PK,PRODENTITY_ID,NEXT_PROCESS_DT,LAST_UPDATE_DT,LAST_UPDATE_USER) VALUES (?,?,?,?,?,?);

but, I continue to get the error even after hardcoding values into these fields in the preceding TX stage.

So, what is parameter 1?

Thanks

chulett · Post by **chulett** » Mon Jun 15, 2009 3:20 pm

So... have you been using this ODBC connection for some time now and this issue is only with this particular job or is this newly set up and it is the first time you are attempting to use it?

bobyon · Post by **bobyon** » Mon Jun 15, 2009 3:32 pm

I do not believe this ODBC connection has worked before for insert or update. However it is used to read a different table in this job and that works just fine. job is

ODBC--------> TX --------> ODBC

Your note makes me think something is amiss in uvodbc.config or .odbc.ini; but what. Your suspicions are correct though this is a new DataStage installation.

chulett · Post by **chulett** » Mon Jun 15, 2009 3:58 pm

That was why I was asking, to narrow this down to either the definition of the ODBC entry or to something specific to the job / query. I would triple-check the former before investigating the latter.

sjfearnside · Post by **sjfearnside** » Tue Jun 16, 2009 8:23 am

If you can't find the problem then just copy the layout of your odbc.ini and uvodbc.config file in your response and maybe we can help. The odbc.ini file will be in your DSEngine directory. There should be an uvodbc.config file in you project specific directory, if not then the one in the DSEngine directory is used.

sjfearnside · Post by **sjfearnside** » Tue Jun 16, 2009 10:35 am

Look at the parameter - #$DB2DATABASE# - in the insert statement and make sure it is specified correctly. Reference the 'DataStage Connectivity Guide for ODBC', PDF pg 19 for information about using parameters with the ODBC connections.

bobyon · Post by **bobyon** » Tue Jun 16, 2009 1:23 pm

The document you reference is about the ODBC Connector stage however, we are using the ODBC Enterprise stage. But I did take your advice and for now I modified the job to eliminate the use of as many parms as possible. I also ensured the bind had been done .

But since I have not solved it myself yet I am following your other suggestions. Here are what I think are the pertinent pieces:

From the OSH script for this stage:

Code: Select all

#### STAGE: DB2_PROCESSCONTROL
## Operator
odbcupsert
## Operator options
-db_cs [&DSProjectMapName]
-data_source 'DB2DSNQ'
-user '[&"$MDM_DB2USER"]'
-password '[&"$MDM_DB2PASSWD"]'
-insert 'INSERT INTO M80QA1.PROCESSCONTROL (PROCESSCON_ID,PROCESSCON_INST_PK,PRODENTITY_ID,NEXT_PROCESS_DT,LAST_UPDATE_DT,LAST_UPDATE_USER) VALUES (?,?,?,?,?,?);'
## General options
.
.
.

From the uvodbc.config file in the project directory:

Code: Select all

<DB2DSNQ>
DBMSTYPE = ODBC

From the .odbc.ini file (I changed the IP address for this post):

Code: Select all

[DB2DSNQ]
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMdb222.so
Description=DB2DSNQ DataDirect DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=
Database=DB2DSNQ
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=172.999.999.999
IsolationLevel=CURSOR_STABILITY
Location=
LogonID=
Password=
Package=
PackageOwner=
TcpPort=6200
WithHold=1

I'm still hoping to learn what "parameter 1" in the error message is referring to. Is it [&DSProjectMapName] ?

sjfearnside · Post by **sjfearnside** » Tue Jun 16, 2009 1:59 pm

Chapter 4 is the ODBC enterprise Stage and Chapter 5 is the ODBC stage.

sjfearnside · Post by **sjfearnside** » Tue Jun 16, 2009 2:17 pm

After the parameters are removed and you hard code the values, what error message are you receiving?

bobyon · Post by **bobyon** » Tue Jun 16, 2009 2:19 pm

same error:

Code: Select all

[ODBC DB2 Wire Protocol driver]Value has not been specified for parameter 1.

sjfearnside · Post by **sjfearnside** » Tue Jun 16, 2009 2:25 pm

What version of DS are you using? Have you checked the IBM FixCentral site to ensure you have all of the relevant patches successfully installed?

sjfearnside · Post by **sjfearnside** » Tue Jun 16, 2009 3:28 pm

I compared your odbc.ini file to my own and the only difference I see is that the Location parameter in your file is blank where mine is set to the DB name and is working. Try setting the 'Location=' parameter in the odbc.ini file to the DB name => DB2DSNQ and try it to see if that makes a difference.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 16, 2009 4:42 pm

I believe that Parameter1 refers to one of the arguments of the ODBC API function SQLSetConnectOption(). This is used, among other things, to establish the operating system credentials for the connection. Find out more about this function in ODBC API documentation or UniVerse BCI documentation.

chulett · Post by **chulett** » Tue Jun 16, 2009 8:33 pm

In other words, refer this to your official support provider.

bobyon · Post by **bobyon** » Tue Jun 16, 2009 9:32 pm

I thought that was what Ray said. Thanks for confirming.

I'll open the ticket first thing in the morning.