ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Using Database and Schema name in PASSTHRU Statement

Post new topic  Reply to topic
 Using Database and Schema name in PASSTHRU Statement « View previous topic :: View next topic » 
Author Message
pdmenon
PostPosted: Thu May 05, 2011 2:30 am    Post subject: Using Database and Schema name in PASSTHRU Statement Reply with quote

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
View user's profile Send private message
khudania
PostPosted: Thu May 05, 2011 3:06 am    Post subject: Reply with quote

Apprentice

Joined: 30 Nov 2004
Posts: 43

Quote:
sn1.dbn1.tbl1


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
View user's profile Send private message
smdavies99
PostPosted: Thu May 05, 2011 3:08 am    Post subject: Reply with quote

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
View user's profile Send private message
pdmenon
PostPosted: Thu May 05, 2011 3:32 am    Post subject: Reply with quote

Voyager

Joined: 05 Apr 2010
Posts: 80

khudania wrote:
Quote:
sn1.dbn1.tbl1


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
View user's profile Send private message
khudania
PostPosted: Thu May 05, 2011 3:41 am    Post subject: Reply with quote

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
View user's profile Send private message
khudania
PostPosted: Thu May 05, 2011 4:05 am    Post subject: Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Thu May 05, 2011 4:26 am    Post subject: Reply with quote

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
View user's profile Send private message
pdmenon
PostPosted: Thu May 05, 2011 5:02 am    Post subject: Reply with quote

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
View user's profile Send private message
khudania
PostPosted: Thu May 05, 2011 5:08 am    Post subject: Reply with quote

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
View user's profile Send private message
pdmenon
PostPosted: Thu May 05, 2011 6:01 am    Post subject: Reply with quote

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
View user's profile Send private message
pdmenon
PostPosted: Thu May 05, 2011 6:55 am    Post subject: Solved!!!! Using Database and Schema name in PASSTHRU Statem Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Thu May 05, 2011 7:04 am    Post subject: Reply with quote

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
View user's profile Send private message
pdmenon
PostPosted: Thu May 05, 2011 10:40 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Using Database and Schema name in PASSTHRU Statement
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.