SEO

Hi all,

Is it possible to have two connects in the same rexx script to
different DB's?
I have to get data form on DB (with specifics selects and filter out
some values with RExx) and save the results into another DB?
I know from Wscripts and MS-Sql that you can build just two objects
but I did not see something like that in REXX.

Thanks in advance for your help

On Apr 21, 7:57 am, "cber...@gmail.com" <cber...@gmail.com> wrote:
> Hi all,
>
> Is it possible to have two connects in the same rexx script to
> different DB's?
> I have to get data form on DB (with specifics selects and filter out
> some values with RExx) and save the results into another DB?
> I know from Wscripts and MS-Sql that you can build just two objects
> but I did not see something like that in REXX.
>
> Thanks in advance for your help


Not sure if REXX supports it on Win/Aix, but current DB2 versions let
an application connect to two databases if the application uses "Type
2 Connect".

Read about "SET CLIENT CONNECT 2 ..." and "SET CONNECTION ..." in the
DB2 publications.

You may have other options

(a) Consider "federation" which lets DB2 see tables in 'remote'
databases as if they were local tables and your Rexx script is unaware
of the remoteness and continues to use a regular type-1 connect. The
remote database can be from a non-DB2 source (e.g. Oracle etc.).
Requires suitable setup/configuration. This would let you do the
filtering in SQL (and if both databases are DB2 with modern versions,
the filtering can happen at source if you configure it right, if my
memory is correct).


(b) Export from source database, load into target database for
processing (but that may be unsuitable for either/both high volumes,
high frequency activity.

Hope this helps.

On Apr 21, 11:08 am, mike <_lin...@yahoo.com> wrote:
> On Apr 21, 7:57 am, "cber...@gmail.com" <cber...@gmail.com> wrote:
>
> > Hi all,
>
> > Is it possible to have two connects in the same rexx script to
> > different DB's?
> > I have to get data form on DB (with specifics selects and filter out
> > some values with RExx) and save the results into another DB?
> > I know from Wscripts and MS-Sql that you can build just two objects
> > but I did not see something like that in REXX.
>
> > Thanks in advance for your help
>
> Not sure if REXX supports it on Win/Aix, but current DB2 versions let
> an application connect to two databases if the application uses "Type
> 2 Connect".
>
> Read about "SET CLIENT CONNECT 2 ..." and "SET CONNECTION ..." in the
> DB2 publications.
>
> You may have other options
>
> (a) Consider "federation" which lets DB2 see tables in 'remote'
> databases as if they were local tables and your Rexx script is unaware
> of the remoteness and continues to use a regular type-1 connect. The
> remote database can be from a non-DB2 source (e.g. Oracle etc.).
> Requires suitable setup/configuration. This would let you do the
> filtering in SQL (and if both databases are DB2 with modern versions,
> the filtering can happen at source if you configure it right, if my
> memory is correct).
>
> (b) Export from source database, load into target database for
> processing (but that may be unsuitable for either/both high volumes,
> high frequency activity.
>
> Hope this helps.

Hi mike very interesting suggestion and I will keep it in mind in the
future, unfortunately i wont be able to set up the DB environment this
way due to securuty requirement.
I am doing for now in a second step usind the db2cmd and a load of the
data, which does not satisfy me.
I remember a way back I was setting up an environment and using
wscript and an MS- SQL Server and had to create always a new object in
order to connect in one script with two different server.. I am
somehow afraid that REXX just does not support it...

Regards Chris

On Apr 21, 8:57 am, "cber...@gmail.com" <cber...@gmail.com> wrote:
> Hi all,
>
> Is it possible to have two connects in the same rexx script to
> different DB's?
> I have to get data form on DB (with specifics selects and filter out
> some values with RExx) and save the results into another DB?
> I know from Wscripts and MS-Sql that you can build just two objects
> but I did not see something like that in REXX.
>
> Thanks in advance for your help

Here my code:

CONNECTVAR.0 = 1 /* ONLY SETTING ONE OPTION */
CONNECTVAR.1 = 2 /* TYPE 1 CONNECT */

CALL RXFUNCADD 'SQLDBS',
'SQLAR','SQLDBS'
CALL RXFUNCADD
'SQLEXEC','SQLAR','SQLEXEC'
CALL RXFUNCADD
'SQLDB2','SQLAR','SQLDB2'
CALL SQLDBS 'START DATABASE
MANAGER'
/
*
// INITIALIZE GLOBAL
VARIABLES
*/
DBNAME = 'METADB1'
DBNAME1 = 'METADB'
SAY "SETTING CLIENT CONNECT TYPE 2 "
CALL SQLDBS 'SET CLIENT USING :CONNECTVAR'
CALL SQLEXEC 'CONNECT TO' DBNAME
CALL SQLEXEC 'CONNECT TO' DBNAME1

CALL SQLEXEC 'SET CONNECTION' DBNAME
SQLCMD = "SELECT *
"
SQLCMD = SQLCMD || "FROM USER01.GRP_MEMBERS
"

CALL SQLEXEC "DECLARE C1 CURSOR FOR
S1"
CALL SQLEXEC "PREPARE S1
FROM :SQLCMD"
CALL SQLEXEC "DESCRIBE S1
INTO :OUTSQLDA"
CALL SQLEXEC "OPEN
C1"
OUTSTEM.0 =
1
DO UNTIL(SQLCA.SQLCODE <>
0)
CALL SQLEXEC "FETCH C1 USING
DESCRIPTOR :OUTSQLDA"
IF SQLCA.SQLCODE = 0 THEN
DO
SAY SEP || OUTSQLDA.1.SQLDATA || SEP SEP || OUTSQLDA.
2.SQLDATA || SEP SEP ||OUTSQLDA.3.SQLDATA || SEP

END

END
CALL SQLEXEC "CLOSE
C1"

CALL SQLEXEC 'SET CONNECTION' DBNAME1

SQLCMD1 = "SELECT *
"
SQLCMD1 = SQLCMD || "FROM
USER01.TCEOCLASSES"

CALL SQLEXEC "DECLARE C1 CURSOR FOR
S1"
CALL SQLEXEC "PREPARE S1
FROM :SQLCMD1"
CALL SQLEXEC "DESCRIBE S1
INTO :OUTSQLDA"
CALL SQLEXEC "OPEN
C1"
OUTSTEM.0 =
1
DO UNTIL(SQLCA.SQLCODE <>
0)
CALL SQLEXEC "FETCH C1 USING
DESCRIPTOR :OUTSQLDA"
IF SQLCA.SQLCODE = 0 THEN
DO
say SEP || OUTSQLDA.1.SQLDATA || SEP SEP || OUTSQLDA.
2.SQLDATA || SEP SEP ||OUTSQLDA.3.SQLDATA || SEP

END

END
CALL SQLEXEC "CLOSE
C1"

CALL SQLEXEC "CONNECT RESET"
EXIT

I GOT THIS ERRORS...

SQL0843N The server name does not specify an existing connection.
SQLSTATE=08003

ANY IDEA?