Re: Accessing multiple databases

[DaveL Smith <davesmit@US.IBM.COM>]

Mark,

Here is a snippet:

!*****************************************************************************
! Procedure "select-data" selects the purchase requisition data      *
! from Maximo tables (SQL Server 6.5)
                                                       *
!*****************************************************************************

begin-procedure select-data

begin-select -DB=MAXTEST;DSN=MAXTEST;UID=MAXIMO;PWD=MAXIMO

A.ITEMNUM,          !Item id
B.PR1               !Routing id.

      let $PR1=nvl(&B.PR1,'E')
      let $ITEMNUM=&A.ITEMNUM

     <snip various other variable assignments>

     DO PS_EL_MX_AL_PR_STG

FROM      PRLINE A, PR B
WHERE     A.PRNUM = B.PRNUM
AND       A.RLIN1 IS NULL
AND       B.STATUS = 'APPR'

end-SELECT

end-procedure select-data

!*****************************************************************************
! The "PS_MX_PR_STG" procedure inserts the Maximo purchase               *
! requisition data into the PS_MX_PR_STG staging table. (SQL Server 7.0)
                  *
!*****************************************************************************

begin-procedure PS_MX_PR_STG

begin-sql -DB=PSFTTEST;DSN=PSFTTEST;UID=PSFTADMIN;PWD=PSFTADMIN

INSERT INTO PS_MX_PR_STG
(SYSTEM_SOURCE, LOADER_BU, REQUESTOR_ID, BUSINESS_UNIT, BU_PRIORITY, SHIPTO_ID,
ORIGIN, REQ_STATUS, CHARTFIELD_STATUS)
VALUES
('FRM',    $STORELOC, $LA2, $STORELOC, 0, ' ',  ' ', ' ',' ')
end-sql

end-procedure PS_MX_PR_STG


This seems to work well with small amounts of data, we run it with SQL Server
agent every 30 minutes.

David L. Smith
IBM Global Services