DB2 UDB API pointing to right schema
Moderators: chulett, rschirm, roy
DB2 UDB API pointing to right schema
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.
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.
-
- Premium Member
- Posts: 278
- Joined: Wed Oct 03, 2007 8:45 am
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.
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.
-
- Premium Member
- Posts: 278
- Joined: Wed Oct 03, 2007 8:45 am
-
- Premium Member
- Posts: 278
- Joined: Wed Oct 03, 2007 8:45 am
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)
-
- Premium Member
- Posts: 278
- Joined: Wed Oct 03, 2007 8:45 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.
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.
-
- Premium Member
- Posts: 278
- Joined: Wed Oct 03, 2007 8:45 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.
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.
Revisiting this issue again
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.
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.