| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Different Oracle schemas from message flow? |
« View previous topic :: View next topic » |
| Author |
Message
|
| vmcgloin |
 Posted: Wed Feb 04, 2004 7:35 am Post subject: Different Oracle schemas from message flow? |
 |
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
Hi,
Is there any way of specifying schema names via the odbc.ini file? I have a flow accessing an Oracle database. So far we have used a generic Datasource name and can just modify the odbc.ini file in different environments. The tables used all have public synonyms in production and have done so far in pre-prod. Now two different environments using the PRE_PROD database want to update different schemas. Any suggestions?
We want to avoid having different versions of the message flow in the various environments so hard-coding the schema in the message flow is not really an option.
[SECUREFORMS]
Driver=/usr/opt/mqsi/merant/lib/UKor818.so
WorkArounds=536870912
WorkArounds2=2
Description=Oracle8
ServerName=PRE_PROD
EnableDescribeParam=1
OptimizePrepare=1
I have tried 'ServerName=PRE_PROD.schemaname' but that didn't work.
I have found one previous post on this subject but that didn't get answered:
http://www.mqseries.net/phpBB2/viewtopic.php?t=4186&highlight=odbc+schema
Thanks!
Vicky |
|
| Back to top |
|
 |
| kirani |
| Posted: Thu Feb 05, 2004 11:45 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Can you post sample ESQL statement with Schema names?
I think you can store the schema names in some persistent storage(Database, File, Environment variable, queue, etc.) and then use it in combination with EVAL statement to generate dynamic query to access different schemas.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| vmcgloin |
| Posted: Fri Feb 06, 2004 1:36 am Post subject: |
|
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
Thanks. They are mainly just basic insert statements like:
| Code: |
INSERT INTO Database.PREMIUM_UPLOAD ( ID, PAYROLL_PERIOD, PAYMENT_METHOD, PAYMENT_DATE, TOTAL_AMOUNT )
VALUES ("Environment"."SecureFormsID"."NEXTVAL", "Body"."premium_upload"."payroll_period", "Body"."premium_upload"."payment_method", "Body"."premium_upload"."payment_date", "Body"."premium_upload"."total_amount"); |
Some use PASSTHRU to get sequence numbers:
| Code: |
| SET Environment.SwitchID[] = PASSTHRU('select SECURE_FORMS_ID.nextval from dual'); |
The above are not qualified with schema names because they use public synonyms to access a 'default' schema. I will try qualifying them with schema names using an extra field in the input message & EVAL.
Cheers,
Vicky |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Feb 06, 2004 3:08 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
If you can, do NOT use EVAL to do this. For justifiable reasons, it is the slowest statement in the whole ESQL language,as it is, in effect, a nested compute note. If you can avoid it, either with { ... } in paths, or, in this case, with PASSTHRU you should.
PASSTHRU and expressions in field references ( { ... } ) are orders of magnitude faster than EVAL. Therefore, whilst there are good uses for EVAL, it should be avoided whereever possible.
e.g. in your case, you can extend the following sample...
DECLARE schema CHAR 'TestSchema';
PASSTHRU('INSERT INTO ' || schema || '.PREMIUM_UPLOAD ( ID) VALUES(?)', Environment.SecureFormsID.NEXTVAL );
However, where you can, you should use the brokers native DB syntax (select and friends) instead of PASSTHRU
Cheers,
MGK
_________________
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 |
|
|
| seeknee |
| Posted: Tue Feb 10, 2004 7:08 am Post subject: |
|
|
Apprentice
Joined: 08 Aug 2002
Posts: 41
Location: Melbourne, Australia
|
Hi
Have you tried to use promote properties.
Hard code the production schema in all nodes that use the database.
Promote the data source for each of these nodes up to the highest level - (upper most message flow)
At the highest level in the data source insert the scheme for the environment you are about to promote the code to.
Change the scheme name in one place,
so each time you promote the flows change the data source.
Hopefully this helps
 |
|
| 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
|
|
|
|
