DB2 UDB API pointing to right schema

neena · Post by **neena** » Fri Jan 18, 2013 1:43 pm

Hi all, can anyone please let me know how does the schema name gets passed to DB2 UDB API stage?

In the userdefined SQL query there is only table name mentioned but when I export the job as XML and checked it's showing up as "DW\.Table name".
I understand for the ODBC driver you set the AlternateID = DW but I think DB2 UDB API doesn't use the ODBC connection instead it's just uses the native drivers.

Issue on DB2 UDB API:

I have Development and Acceptance on same server. From Development all the DB2 connections work fine, but in Acceptance when I do a view data in DB2 UDB API stage it's not able to point to right schema. So I am wondering if there some where at project level its able to recognize the schema.

I have done lot of search but couldn't able to find any information. Please let me know if I am missing anything.

sjfearnside · Post by **sjfearnside** » Fri Jan 18, 2013 3:00 pm

In our shop we use parameters or parameter sets to pass the DB name, Schema, User ID and Password to the DB2 API call.

neena · Post by **neena** » Fri Jan 18, 2013 3:52 pm

Thanks, we are using parameters sets, but I can see in SQL statements that there is no where schema name is passed as parameter.

For testing purpose we have created a test project and done the full project import and then when I do the view data it is pointing to the right schema.

Just for the Note: When I imported jobs to the Acceptance (where I am currently having issue) I haven't done full project import instead I have just imported the jobs. I thought it's better I mention this.

sjfearnside · Post by **sjfearnside** » Fri Jan 18, 2013 3:56 pm

Did you import the parameter sets?

neena · Post by **neena** » Fri Jan 18, 2013 4:03 pm

Yes I did. But please let me understand what that has to do with the parameter sets? I don't even have the schema name parametrized.

sjfearnside · Post by **sjfearnside** » Fri Jan 18, 2013 4:09 pm

Our shop uses Oacle and the DBA's setup what they called an alias so no reference to a schema is required.

You may want to check with your DBA to see if that is the case.

neena · Post by **neena** » Fri Jan 18, 2013 4:29 pm

I am actually working with the DBA. We see that connects are set properly because we were able to point to correct schema (even though there is no schema parameter) in Development and Test Project. When we are doing View data the only thing we are changing is the DB2 server name (nothing but Alias name for each connection that we created for native drivers)

sjfearnside · Post by **sjfearnside** » Mon Jan 21, 2013 7:36 am

Does the Dev and Acceptance environments use the same DB2 database or are there separate DB2 databases for each environment?

neena · Post by **neena** » Mon Jan 21, 2013 7:37 am

New improvement to this. I have deleted all the jobs that are in the acceptance (where I am having issue) and then imported the whole project from Development to Acceptance then the DB2 UDB API stage is pointing to the right schema.
Which even more convinced me that some object in the datastage that is storing the value where it is making the DB2 UDB API stage to point to the right schema.

sjfearnside · Post by **sjfearnside** » Mon Jan 21, 2013 7:44 am

If you are using dsx files to transfer the projects for Dev to Acceptance you can search the dsx dataset and find any reference to the DB2 call and see if it will reveal how the information is being passed. If you find it, I would be interested in what you find out.

Thanks

neena · Post by **neena** » Mon Jan 21, 2013 8:41 am

I have been transferring in XML format to view the details. But I haven't find anything yet.
I think this would be worth mentioning. We are actually migrating from Windows to Red Hat Linux server. As a part of this migration I have move the entire project from Windows to Linux as a Development project and created the DB connections. At this point all the jobs were working fine.

Next step I created the Acceptance project but this time when I move only the jobs I had this issue to point to the right schema.

I am not sure if migrating the entire thing from Windows had any effect on this issue.

neena · Post by **neena** » Tue Feb 26, 2013 11:25 am

our DBA has created connections to DB2 by cataloging in the production.

For testing purpose I have migrated one job which has the DB2 API stage to production to check the connection settings are working. In this stage we are not passing schema name to the table.

When I do a view data on the DB2 stage it gave an error that UserID.Tablename (xxx.Table name) is an unidentified object. I even tried importing table definitions for this particular table that I am accessing but no luck.

If I migrate the entire project I am sure that this issue will not come (that's how I mitigated the issue in Acceptance) but I want to understand where does the schema name getting passed to DB2 API stage this time around.

Can anyone please give me any advice where to start from.

neena · Post by **neena** » Thu Feb 28, 2013 5:01 pm

we have solved this issue by editing the file db2cli.ini which has the schema information for the DB2 native connection. Basically the default schema is set in this file which will be used by the DB2 API stage.

DSXchange

DB2 UDB API pointing to right schema

DB2 UDB API pointing to right schema

Revisiting this issue again