Author |
Message
|
pdmenon |
Posted: Thu May 05, 2011 2:30 am Post subject: Using Database and Schema name in PASSTHRU Statement |
|
|
 Voyager
Joined: 05 Apr 2010 Posts: 80
|
Dear Experts,
We are trying to use "database"."schema"."tablename" in PASSTHRU Statement, using the following code:
Code: |
SET DBName = 'dbn1';
SET SchemaName = 'sn1';
SET TableName = 'tbl1';
SET ConditionList = ' WHERE cond1= ''' || CH_CRS || ''' AND cond2= ''' || CH_BRANCH || '''';
SET DUMSHDQuery = 'SELECT DISTINCT col1,col2 FROM ' || SchemaName || '.' || DBName || '.' || TableName || ConditionList ;
SET Environment.Variables.DUMSHDatabase[]= PASSTHRU(DUMSHDQuery); |
The above code giving us the following error:
Code: |
Text:CHARACTER:[IBM][CLI Driver][DB2/AIX64] SQL0204N "sn1.dbn1.tbl1" is an undefined name. SQLSTATE=42704 |
We checked the following post too:
http://www.mqseries.net/phpBB2/viewtopic.php?p=296388&sid=e233a13d52b480988ae642ee2a777723
But still facing this error.
We are using MB 6.1.0.4.
Kindly help. |
|
Back to top |
|
 |
khudania |
Posted: Thu May 05, 2011 3:06 am Post subject: |
|
|
 Apprentice
Joined: 30 Nov 2004 Posts: 43
|
Dont you think it should have been
Code: |
dbn1.sn1.tbl1
or
sn1.tbl1 |
I mean, how come db name preceded by schema, it should hv been opposite instead. _________________ If the doors of perception were cleansed, everything would appear as it is - infinite
Last edited by khudania on Thu May 05, 2011 3:16 am; edited 1 time in total |
|
Back to top |
|
 |
smdavies99 |
Posted: Thu May 05, 2011 3:08 am Post subject: |
|
|
 Jedi Council
Joined: 10 Feb 2003 Posts: 6076 Location: Somewhere over the Rainbow this side of Never-never land.
|
A quick google for 'db2 sqlsate=47202' gives (amongst many)
http://www.ibm.com/developerworks/forums/thread.jspa?messageID=13977113
Quote: |
Since a SQLSTATE of 42704 indicates that the table does not exist
|
Did you try without all the parts
"dbn1.tbl1"
or
"tbl1"
Are you really sure that the table exists?
Is there are reason for using PASSTHRU rather than an ESQL Select? _________________ WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
Back to top |
|
 |
pdmenon |
Posted: Thu May 05, 2011 3:32 am Post subject: |
|
|
 Voyager
Joined: 05 Apr 2010 Posts: 80
|
khudania wrote: |
Dont you think it should have been
Code: |
dbn1.sn1.tbl1
or
sn1.tbl1 |
I mean, how come db name preceded by schema, it should hv been opposite instead. |
Apologies!!! It was Typo. It is "dbn1.sn1.tbl1".
smdavies99 wrote: |
Did you try without all the parts
"dbn1.tbl1"
or
"tbl1"
|
Dear Davies,
We are trying to connect to two databases from one compute node.
We have already set one Data Source in the properties of that compute node. So in order to access the second database we need to follow the notation databaseName.schemaName.tableName
Quote: |
Are you really sure that the table exists?
Is there are reason for using PASSTHRU rather than an ESQL Select? |
Yes. We need to use DISTINCT in our query and "Select" and "THE" statements do not support "DISTINCT" |
|
Back to top |
|
 |
khudania |
Posted: Thu May 05, 2011 3:41 am Post subject: |
|
|
 Apprentice
Joined: 30 Nov 2004 Posts: 43
|
Quote: |
Apologies!!! It was Typo. It is "dbn1.sn1.tbl1". |
I dont think its a typo, your code is doing the same...
Code: |
SET DUMSHDQuery = 'SELECT DISTINCT col1,col2 FROM ' || SchemaName|| '.' || DBName || '.' || TableName || ConditionList ; |
and I am sure you dint type the error..
Quote: |
ext:CHARACTER:[IBM][CLI Driver][DB2/AIX64] SQL0204N "sn1.dbn1.tbl1" is an undefined name. SQLSTATE=42704 |
I think you should modify the code once and then try and post whats the new error.
for eg
Code: |
SET DUMSHDQuery = 'SELECT DISTINCT col1,col2 FROM ' || DBName || '.' || SchemaName|| '.' || TableName || ConditionList |
_________________ If the doors of perception were cleansed, everything would appear as it is - infinite |
|
Back to top |
|
 |
khudania |
Posted: Thu May 05, 2011 4:05 am Post subject: |
|
|
 Apprentice
Joined: 30 Nov 2004 Posts: 43
|
Quote: |
We are trying to connect to two databases from one compute node.
We have already set one Data Source in the properties of that compute node. So in order to access the second database we need to follow the notation databaseName.schemaName.tableName
|
Is this possible ? One Datasource for two databases ? _________________ If the doors of perception were cleansed, everything would appear as it is - infinite |
|
Back to top |
|
 |
mqjeff |
Posted: Thu May 05, 2011 4:26 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 17447
|
It's possible to connect to more than one database from the same compute node. They do have to be the same TYPE of database (DB2, etc.), however.
Whether or not you can point the same DSN at more than one database is a separate question...
I think pdmenon should review the documentation on the PASSTHRU and better understand what it passes across to the database and what it uses to determine which database TO call.... |
|
Back to top |
|
 |
pdmenon |
Posted: Thu May 05, 2011 5:02 am Post subject: |
|
|
 Voyager
Joined: 05 Apr 2010 Posts: 80
|
khudania wrote: |
Quote: |
Apologies!!! It was Typo. It is "dbn1.sn1.tbl1". |
I dont think its a typo, your code is doing the same...
Code: |
SET DUMSHDQuery = 'SELECT DISTINCT col1,col2 FROM ' || SchemaName|| '.' || DBName || '.' || TableName || ConditionList ; |
and I am sure you dint type the error..
Quote: |
ext:CHARACTER:[IBM][CLI Driver][DB2/AIX64] SQL0204N "sn1.dbn1.tbl1" is an undefined name. SQLSTATE=42704 |
I think you should modify the code once and then try and post whats the new error.
for eg
Code: |
SET DUMSHDQuery = 'SELECT DISTINCT col1,col2 FROM ' || DBName || '.' || SchemaName|| '.' || TableName || ConditionList |
|
Hi,
I tried the following code:
Code: |
SET SchemaName = 'sn1';
SET DBName = 'dbn1';
SET TableName = 'tbl1';
SET ConditionList = ' WHERE CRS = ''' || CH_CRS || ''' AND BRANCH = ''' || CH_BRANCH || '''';
SET DUMSHDQuery = 'SELECT DISTINCT CRS, BRANCH FROM ' || DBName || '.' || SchemaName || '.' || TableName || ConditionList ;
SET Environment.Variables.DUMSHDatabase[]= PASSTHRU(DUMSHDQuery); |
And it gave me the following error:
Code: |
Text:CHARACTER:[IBM][CLI Driver][DB2/AIX64] SQL0204N "dbn1.sn1.tbl1" is an undefined name. SQLSTATE=42704 |
[/quote] |
|
Back to top |
|
 |
khudania |
Posted: Thu May 05, 2011 5:08 am Post subject: |
|
|
 Apprentice
Joined: 30 Nov 2004 Posts: 43
|
Did you try this.
Code: |
SET Environment.Variables.DUMSHDatabase[]= PASSTHRU(DUMSHDQuery TO Database.DSN2 ); |
Here DSN2 is the Other Datasource. _________________ If the doors of perception were cleansed, everything would appear as it is - infinite |
|
Back to top |
|
 |
pdmenon |
Posted: Thu May 05, 2011 6:01 am Post subject: |
|
|
 Voyager
Joined: 05 Apr 2010 Posts: 80
|
khudania wrote: |
Did you try this.
Code: |
SET Environment.Variables.DUMSHDatabase[]= PASSTHRU(DUMSHDQuery TO Database.DSN2 ); |
Here DSN2 is the Other Datasource. |
Dear Khudania,
I will definitely check this out and get back to you.
Thanks. |
|
Back to top |
|
 |
pdmenon |
Posted: Thu May 05, 2011 6:55 am Post subject: Solved!!!! Using Database and Schema name in PASSTHRU Statem |
|
|
 Voyager
Joined: 05 Apr 2010 Posts: 80
|
pdmenon wrote: |
khudania wrote: |
Did you try this.
Code: |
SET Environment.Variables.DUMSHDatabase[]= PASSTHRU(DUMSHDQuery TO Database.DSN2 ); |
Here DSN2 is the Other Datasource. |
Dear Khudania,
I will definitely check this out and get back to you.
Thanks. |
Dear Khudania,
It worked Fine. Thanks a lot.  |
|
Back to top |
|
 |
mqjeff |
Posted: Thu May 05, 2011 7:04 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 17447
|
So when you reviewed the documentation on the PASSTHRU command, including what the TO Parameter of it does, then you reached an understanding of why it worked? |
|
Back to top |
|
 |
pdmenon |
Posted: Thu May 05, 2011 10:40 pm Post subject: |
|
|
 Voyager
Joined: 05 Apr 2010 Posts: 80
|
mqjeff wrote: |
So when you reviewed the documentation on the PASSTHRU command, including what the TO Parameter of it does, then you reached an understanding of why it worked? |
Dear Jeff,
Yes, I reviewed the documentation on the PASSTHRU statement and implemented the same: It worked fine.
Code: |
SET Environment.Variables.DUMSHDatabase[]= PASSTHRU('SELECT S.COL1, S.COL2 FROM SN1.TBL1 AS S WHERE S.COL1 = ? AND S.COL2 = ?' TO Database.DSN1 VALUES (COL1 VAR,COL2 VAR));
|
Thanks for all the replies.
 |
|
Back to top |
|
 |
|