| Author |
Message
|
| pcelari |
 Posted: Wed Apr 11, 2007 6:09 am Post subject: difficulty with propagate MessageSource clause |
 |
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
I use a computeNode to call a stored procedure and construct output msg using the resultset. If SP returns none-zero, meaning no record found, I would need to call a second SP in a warehouse DB where old records are archived.
Since One ComputeNode can associate with only one database, I wire the out1 terminal with a second ComputeNode to call the second SP. I use a propagate statement to route the same inputRoot to the out1 terminal.
I use the MessageSource clause to specify InputRoot be route to 'out1', as stated in the document. But it got rejected in the ESQL editor.
if (ret_code<>0) then
propagate to terminal 'out1' MessageSource InputRoot;
return false;
end if;
I read a few threads related to 'propagate' statement, but have found none using the MessageSource clause. Am I missing sth?
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Apr 11, 2007 6:18 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
This may be a bug in the Toolkit - make sure you are running 6.0.2.0 and the latest IFixes. There are some known issues with the syntax handling of the Propagate function.
You can work around this, and find out if your code is functional as written, by putting it into executeable comments.
/*!{ Propagate to terminal 'out1' MessageSource InputRoot; }!*/
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/topic/com.ibm.etools.mft.doc/ak04870_.htm#ak04870___comments
If this functions, and runs on broker, then please open a PMR and let the Toolkit development team know about the problem.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Apr 11, 2007 7:14 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
It would also help if you took another look at the syntax diagram in the docs. There is no 'MessageSources' verb in the sytax, it simply represents one or more of the Environment, Message, Exception verbs as in this example:
| Code: |
| PROPAGATE TO TERMINAL 'out1' MESSAGE InputRoot; |
or
| Code: |
| PROPAGATE TO TERMINAL 'out1' MESSAGE InputRoot ENVIRONMENT InputLocalEnvrionment; |
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| pcelari |
| Posted: Wed Apr 11, 2007 7:26 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
thank you mgk, it did the trick.
BTW, what should be the right name of a Out1 terminal, 'Out1' is the name listed, but many thread about this say 'out1' is the right name. Why?
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| Bill.Matthews |
| Posted: Wed Apr 11, 2007 9:15 am Post subject: |
|
|
Master
Joined: 23 Sep 2003
Posts: 232
Location: IBM (Retired)
|
| pcelari wrote: |
thank you mgk, it did the trick.
BTW, what should be the right name of a Out1 terminal, 'Out1' is the name listed, but many thread about this say 'out1' is the right name. Why? |
the name is all lower case
_________________
Bill Matthews |
|
| Back to top |
|
|
| sknrt1 |
| Posted: Wed Apr 11, 2007 12:33 pm Post subject: |
|
|
Apprentice
Joined: 22 Jan 2003
Posts: 39
Location: USA
|
peclari wrote
| Quote: |
Since One ComputeNode can associate with only one database[/code] |
Its possible to connect to multiple database's /DSN's from one compute node. In passthru can specify the DSN against which SQL should be executed. for e.g.
-----------------------------
| Code: |
DECLARE sqlStmt CHAR;
DECLARE dsnN NAME InputRoot.xx.xx.xx;
SET sqlStmt = 'SELECT T.* FROM schema.table AS T;
/*Executable Comments to bypass Toolkit compilation problem }!*/
SET EV.Result[] = PASSTHRU (sqlStmt TO Database.dsnN); |
------------------------------
In the above code, dsn name was obtained dynamically from input data. Part of 6.0.0.1 toolkit, had this code under Executable comments to avoid toolkit compilation problem, but with 6.0.2 no compilation problems.
Thanks |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Apr 11, 2007 12:38 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
The databases must be of the same type, though - all DB2, all Oracle, all Sybase, etc.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| pcelari |
| Posted: Thu Apr 12, 2007 6:19 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
that's a great insight for me, thank you!
I need to call a different stored procedure in another warehouse DB, all DB2. How can I pass the SP call, with parameters in a passthru statement?
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Apr 12, 2007 7:43 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
You don't need to. In V6 ALL the ESQL statements and functions that access databases (such as SELECT, UPDATE, INSERT, PASSTHRU etc) have been enhanced to allow a dynamic database DSN name or Table name or Schema Name to be defined. In the case of stored procedures, look up the CALL statement in the docs for details on how to do this...
It will look something like this (untested):
| Code: |
| CALL myStoredProc( myParams ) IN DATABASE myDSN.mySchema; |
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| pcelari |
| Posted: Thu Apr 12, 2007 4:55 pm Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
thanks for the info. It opens a new door for my restricted perceptions.
So does it means I don't even need to specify the data source in my compute node property to associate it with a specific DB?
I just need to run the following command several times, each associate the broker with a different DB?
mqsisetdbparms MYBKR -n MYDB1 -u userid -p userpwd
mqsisetdbparms MYBKR -n MYDB2 -u userid -p userpwd
mqsisetdbparms MYBKR -n MYDB3 -u userid -p userpwd
and call the corresponding SP when needed with the syntax:
CALL mySP1( myParams ) IN DATABASE MYDB1;
CALL mySP2( myParams ) IN DATABASE MYDB2;
CALL mySP3( myParams ) IN DATABASE MYDB3;
Mine is on zLinux platform, no DSN is needed, as only DB2 is supported, I just have to create a client catalog for each remote DB, all on z/OS.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Apr 12, 2007 5:55 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You need to specify a DSN on the compute node in order to associate it with a specific DB type. You might as well point it to the main database you're going to use.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| pcelari |
| Posted: Mon Apr 16, 2007 12:20 pm Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
With the following commands, will the broker become associated with all three DBs, or just the last one?
To my understanding, the most recent one overwrite the last setting.
| Quote: |
mqsisetdbparms MYBKR -n MYDB1 -u userid -p userpwd
mqsisetdbparms MYBKR -n MYDB2 -u userid -p userpwd
mqsisetdbparms MYBKR -n MYDB3 -u userid -p userpwd |
thanks for clarifying this.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Apr 16, 2007 12:25 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Yes, only the last one.
but, really, this has nothing to do with 'associating' the broker with a DSN. It only tells broker which userid and password to use when connecting to a given DSN.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| pcelari |
| Posted: Mon Apr 16, 2007 2:50 pm Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
| jefflowrey wrote: |
| It only tells broker which userid and password to use when connecting to a given DSN. |
So that means, in order for a msgflow to access multiple remote databases, they all have to accept the same userid and password?
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Apr 16, 2007 3:44 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
No.
It means that broker will only ever use one user name and password for a given DSN.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
