| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Not able to access the Database using select statement |
« View previous topic :: View next topic » |
| Author |
Message
|
| indu30111986 |
 Posted: Tue Dec 27, 2011 6:43 am Post subject: Not able to access the Database using select statement |
 |
|
Novice
Joined: 15 Nov 2011
Posts: 22
|
CREATE COMPUTE MODULE sample_DBconn_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE LIKE '%' BEGIN END;
CALL CopyEntireMessage();
DELETE FIELD OutputRoot.*[<];
SET OutputRoot.XML.Test.Result[] = SELECT T.* FROM Database.TABLE1 AS T;
-- CALL CopyMessageHeaders();
RETURN TRUE;
END;
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
END MODULE;
For the above code am getting a warning
Description Resource Path Location Type
Unresolvable database table reference "Database.TABLE1". sample_DBconn.esql Airline XML Message Flows line 10 ESQL Loading Failure of Referenced File
I have created the DSN in ODBC manager and have refered the same in cmpute node property.
Let me know the solution pls. |
|
| Back to top |
|
 |
| bruce2359 |
| Posted: Tue Dec 27, 2011 6:52 am Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
Moved to Broker forum.
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Dec 27, 2011 7:02 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Did you complete these tasks?
| Quote: |
Complete the following tasks to set up the database environment:
Create the database and ODBC definition.
Follow the instructions that are relevant to your database manager:
Setting up DB2
These instructions assume that you are using DB2 Universal Database and guide you through the following tasks:
Create a database called RESERVDB for use by the Airline Reservations sample
Bind the DB2 CLI packages
Create tables called XMLFLIGHTTB and XMLPASSENGERTB for use by the Airline Reservations sample
Populate the sample data into the XMLFLIGHTTB table
Create an ODBC definition for the database
Note. When you create and access the RESERVDB tables, be aware of the following issues:
When you run the script that creates the tables in the RESERVDB database, the user identifier from which you run the script determines the database schema name. For example, if your user identifier is USERID, the fully qualified name of the XMLFLIGHTTB table name is USERID.XMLFLIGHTTB.
When you set a node to access a table, if the broker is not using the same user identifier to access the database that you used when you created the database and tables, include the schema name in the node, for example, Database.USERID.XMLFLIGHTTB
Complete the following steps:
Open a new document in a text editor, copy and paste the following script, which creates the RESERVDB database and runs the BIND utility, into the document, name the file airline1.sql and save the file.
On Windows:
You must replace install_dir with the path to your DB2 product installation directory, for example, C:\PROGRA~1\IBM\SQLLIB
This location can be obtained by using the following db2level command:
CREATE DB RESERVDB
CONNECT TO RESERVDB
BIND 'install_dir\bnd\@db2cli.lst' blocking all grant public
TERMINATE
On Linux:
You must replace install_dir with the path to your DB2 product installation directory. This location can be obtained by using the following db2level command:
CREATE DB RESERVDB
CONNECT TO RESERVDB
BIND install_dir/bnd/@db2cli.lst grant public CLIPKG 5
TERMINATE
In a DB2 command window, enter the following command to ensure that DB2 is started:
db2startIn a DB2 command window, navigate to the folder that contains airline1.sql and enter the following command:
db2 -vf airline1.sqlOpen a new document in a text editor, copy and paste the following script, which creates the XMLFLIGHTTB and XMLPASSENGERTB tables and populates the XMLFLIGHTTB table, into the document, name the file airline2.sql and save the file.
CONNECT TO RESERVDB
DROP TABLE XMLFLIGHTTB
CREATE TABLE XMLFLIGHTTB (FLIGHTDATE CHAR( NOT NULL, FLIGHTNO CHAR(5) NOT NULL, ECONOMICCLASS INTEGER NOT NULL, FIRSTCLASS INTEGER NOT NULL, TOTALECONOMIC INTEGER NOT NULL, TOTALFIRST INTEGER NOT NULL, ECONOMICPRICE INTEGER NOT NULL, FIRSTPRICE INTEGER NOT NULL, STARTPOINT CHAR(20) NOT NULL, ENDPOINT CHAR(20) NOT NULL, RESERVATIONSEQNO INTEGER NOT NULL, CONSTRAINT FLIGHT_UNIQ UNIQUE(FLIGHTDATE, FLIGHTNO))
INSERT INTO XMLFLIGHTTB VALUES('20030218', 'CA937', 0, 0, 200, 50, 200, 300, 'BEIJING', 'LONDON', 0)
INSERT INTO XMLFLIGHTTB VALUES('20030525', 'BA039', 0, 0, 180, 40, 220, 320, 'LONDON', 'BEIJING', 0)
INSERT INTO XMLFLIGHTTB VALUES('20030525', 'CA937', 0, 0, 200, 50, 200, 300, 'BEIJING', 'LONDON', 0)
INSERT INTO XMLFLIGHTTB VALUES('20030219', 'BA039', 0, 0, 180, 40, 220, 320, 'LONDON', 'BEIJING', 0)
DROP TABLE XMLPASSENGERTB
CREATE TABLE XMLPASSENGERTB (LASTNAME CHAR(20) NOT NULL, FIRSTNAME CHAR(20) NOT NULL, FLIGHTNO CHAR(5) NOT NULL, FLIGHTDATE CHAR( NOT NULL, CLASSTYPE CHAR(1) NOT NULL, RESERVATIONNO VARCHAR(40) NOT NULL, PRIMARY KEY(RESERVATIONNO))
TERMINATE
In a DB2 command window, navigate to the folder that contains airline2.sql and enter the following command:
db2 -vf airline2.sqlWait for the script to finish running. If you are running the script for the first time, the following message is displayed, where DB2ADMIN is your user name and XMLPASSENGERTB is the name of the table:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line
Processor command. During SQL processing it returned: SQL0204N "DB2ADMIN.XMLPASSENGERTB"
is an undefined name. SQLSTATE=42704Ignore these messages. The messages are displayed because the script attempts to remove any existing tables called XMLFLIGHTTB and XMLPASSENGERTB before it creates the new tables, but if you have not run the script before, the script cannot find any existing tables.
Create an ODBC definition for the RESERVDB database with RESERVDB as the data source name (DSN).
For instructions, see Enabling ODBC connections to the databases in the WebSphere Message Broker documentation.
Specify the user identifier and password that are associated with the data source name RESERVDB.
Use the mqsisetdbparms command to specify a user identifier and password for the broker to use with the RESERVDB data source. This user identifier must be the same user identifier that you used when you created the database in the previous step. The user identifier determines the database schema name and the broker must use the same user identifier when accessing the table. For example, if your user identifier is USERID, the fully qualified name of the XMLFLIGHTTB table name is USERID.XMLFLIGHTTB.
Open a command console window in which you can run WebSphere Message Broker commands. Enter the following command to specify the user identifier and password:
mqsisetdbparms MB7BROKER -n RESERVDB -u <user ID> -p <password>Enter the following command to reload the execution group. Note: This step is required only if you have an existing execution group.
mqsireload MB7BROKER -e AirlineExecutionGroup |
Also, did you source the DB2 profile from the Broker service Id?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Dec 27, 2011 9:32 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
In addition to the very astute comments by lancelotlinc, why are you using the XML domain. This has been deprecated.
| Code: |
SET OutputRoot.XML.Test.Result[]
|
Perhaps you might like to take some formal training.
_________________
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 |
|
|
| indu30111986 |
| Posted: Mon Jan 02, 2012 11:41 pm Post subject: Unresolvable database table reference |
|
|
Novice
Joined: 15 Nov 2011
Posts: 22
|
| lancelotlinc...i am using oracle 10g..i have done the database settings as mentioned by you...but still the warning has not gone.. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Jan 03, 2012 12:26 am Post subject: Re: Unresolvable database table reference |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| indu30111986 wrote: |
Unresolvable database table reference "Database.TABLE1". sample_DBconn.esql Airline XML Message Flows line 10 ESQL Loading Failure of Referenced File
|
This is a warning in the toolkit. To remove it, you have to configure the toolkit to access the Database. The Data perspective of the toolkit will have a wizzard to do this for you. (hint New->Data Development Project)
This is a WARNING. You don't have to do this. IF you have the ODBC connection properly defined AND the correct access to the DB, the runtime will resolve it for you.
My previous statement still applies.
Perhaps you should really think about seeking some formal training.
_________________
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 |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
