As400 ODBC Problem

[pjsimon]

mat,
After fixing the username/password issue. i get .........
./bind20 PRISMDB2CONNECT
Packages created and bound

Now i'm trying to test the ODBC Connectivity and i get the following error...

>DS_CONNECT PRISMDB2CONNECT
Enter username for connecting to 'PRISMDB2CONNECT' DBMS [dsadmin]: c408260
Enter password for c408260:
SQLConnect error: Status = -1 SQLState = S1000 Natcode = -713
[ODBC] [DataDirect][ODBC DB2 Wire Protocol driver][DB2]Unknown error: SQLCODE -713

what do i have to fill in for.... Collection in the .odbc.ini file????? At present i just have AS/400....... Is the above error related to this???


Thanks
Prince.

[LaurelClark]

Prince,

The default port for the as400 is 446.
Below is how our odbc.ini file is configured.

Also you'll need an entry in the uvodbc.config file at the project level if you wish to capture the metadata.
Hope this helps!
Laurel

uvodbc.config entry:

<BRSCJUSA05>
DBMSTYPE = ODBC


odbc.ini entry:

[BRSCJUSA07]
Driver=/etltool/Ascential/DataStage/branded_odbc/lib/VMdb220.so
Description=DataDirect 5.00 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Collection=SQADDLIBLE BRP62UF BRP62LGF BRP62F BRP62U AMP62O AMP62F
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=152.72.253.157
IsolationLevel=CURSOR_STABILITY
Location=SCJUSA07
LogonID=ETLUSER
Password=ETL4AMER
Package=DSPBR
PackageOwner=ETLUSER
TcpPort=446
WithHold=1



[quote="pjsimon"]mat,
After fixing the username/password issue. i get .........
./bind20 PRISMDB2CONNECT
Packages created and bound

Now i'm trying to test the ODBC Connectivity and i get the following error...

>DS_CONNECT PRISMDB2CONNECT
Enter username for connecting to 'PRISMDB2CONNECT' DBMS [dsadmin]: c408260
Enter password for c408260:
SQLConnect error: Status = -1 SQLState = S1000 Natcode = -713
[ODBC] [DataDirect][ODBC DB2 Wire Protocol driver][DB2]Unknown error: SQLCODE -713

what do i have to fill in for.... Collection in the .odbc.ini file????? At present i just have AS/400....... Is the above error related to this???


Thanks
Prince.[/quote]

[pjsimon]

Laurel,

what do i have to fill in for

Package=??
Location=??? (Is it the remote database name??)
Collection=??? (Is it the library names to be filled one after the other???)

Thanks
Prince.

[pjsimon]

Mat,
I got the connectivity working and i could import table definitions.......but when i run an extraction job..........its errors out saying
"QSYS.PRISMPH DOES NOT HAVE PRIVILEGE TO PERFORM OPERATION TABLE ON THIS OBJECT(null)" Is it some security issue pertaining to "Select" access????

Thanks for ur help

[ml]

I guess that is a privileges problem (if you are sure that you have the correct port/location/collection).

Try this, open a telnet sesion, connect to AS400 server, log in with the user and password you are using in DS, run the command STRSQL and run the query you have in your job.

[pjsimon]

This is what i get..........

>Select * from PRISMPH.PMODEL
Qualified object name PMODEL not valid.


But I could import table definitions from PRISMPH.PMODEL......so it is a valid object name.......

[ray.wurlod]

Table definitions require select privileges only to the system tables, not the actual database objects. So it is perfectly possible to import table definitions and yet not be permitted to access the actual database objects.

[ml]

>Select * from PRISMPH.PMODEL
Qualified object name PMODEL not valid.

My friend this error is because in AS400 your query should be "Select * from PRISMPH/PMODEL"

Unfortunately I believe that if you are migrating your jobs from version 6 to version 7 you would need to go to the ODBC stages and change the "." to a "/" in the table name. Not good...

[pjsimon]

Yes it is an authorization issue........

> Select * From PRISMPH/PMODEL
Not authorized to object PRISMPH in QSYS type *LIB.

Thanks

[htang619]

I am trying to set up ODBC DB2 connection by adding the following to odbc.ini file:

[xxerdb]
Driver=/appl/Ascential/DataStage/branded_odbc/lib/VMdb220.so
Description=DataDirect 5.00 DB2 Wire Protocol Driver
AddStringToCreateTable=
AlternateID=
Database=bherdb
DynamicSections=100
GrantAuthid=PUBLIC
GrantExecute=1
IpAddress=xxx.xx.xx.xx
IsolationLevel=CURSOR_STABILITY
LogonID=
Password=
Package=pgerdw
PackageOwner=
TcpPort=51544
WithHold=1

The DB2 is on AIX box. After that, I ran: bind20 xxerdb, and I got this msg: Package creation failed!: 1402. Can you tell what does this mean?

[ml]

The DB2 is on AIX box. After that, I ran: bind20 xxerdb, and I got this msg: Package creation failed!: 1402. Can you tell what does this mean?

Sorry I don't remember the meaning of 1402 error, you can check the description here: *.po files located under /branded_odbc/locale/en_US/LC_MESSAGES

good luck!!

[Chuah]

Hi,
On my Solaris server (running Solaris , when I try to bind20 I got the error

Package Creation Failed : 7742

I can't find that msg in the LC_MESSAGES. Anyone has any ideas what this 7742 code means ?

Chin

[ml]

If you are using the "VMdb220.so" ODBC driver, the error code should be in this file: "ivdb220.po"

[roy]

Parden my "work relational database direct" AS400 syntax, but multiple thread hi-jacking is not the flavor of the month.

There is no point in patching more posts for each problem another member encounters connecting along time.

You can open your own thread mentioning the error number in the post topic or additional info and perhaps link to the original post.

Did anyone even mind the original post dates a year back or so?

Thus it was said and thus this post was locked!

Thanks,
Forum Moderator.