Transcript Main Title Goes Here - Information Builders

Dynamically Loop Through Database
Connections With DataMigrator
Presented by
Kathy Getz, Omnicare Inc.
Lori Pieper, Information Builders Inc.
Agenda





Presenter Introductions
Omnicare Business Overview
Omnicare Architecture
Challenges/Solution
Questions/Answers
Omnicare Business Overview
Omnicare, Inc. OCR, a Fortune 500 company
based in Covington, Kentucky, is a leading
provider of pharmaceutical care for the
elderly. Omnicare serves residents in longterm care facilities and other chronic care
settings comprising approximately 1.4 million
beds in 47 states, the District of Columbia and
Canada. Omnicare is the largest U.S.
provider of professional pharmacy, related
consulting and data management services for
skilled nursing, assisted living and other
institutional healthcare providers as well as
for hospice patients in homecare and other
settings. Omnicare's pharmacy services also
include distribution and patient assistance
services for specialty pharmaceuticals.
Omnicare offers clinical research services for
the pharmaceutical and biotechnology
industries in 30 countries worldwide.
iWay Data Migrator Architectural Diagram
Environmental Challenges

Combining AS/400 data with HP-UX data


Metadata – Table names shared



For reporting from Windows
16 AS/400 database
64 HP-UX Schemas
Standardized Metadata across platforms
Metadata Names by Default
AS/400 Metadata
HP-UX Metadata
Metadata Names Shared
•Standardized naming
conventions
•User Friendly Metadata
Available Options
 Maintain 80 sets of Metadata per Table
Maintain 80 dataflows per iDM job
 Share Metadata and loop through databases
Solution: Share Metadata and
loop through databases
Solution Details

Dynamically Create Access files (.ACX)
AS/400 change table names dynamically
Solution Details

Dynamically Create Access files (.ACX)
HP-UX change connection dynamically
Dynamically Creating Access Files

Controlling program obtains system connection
information

AS/400
 Changing

“Tablename” Parameter
HP-UX
 Changing
“Connection” Parameter
Setting owner name value
Setting table name value
Required file def for .ACX file
Required
Writing .ACX file
Setting connection value
Required file def for .ACX file
Required
Writing .ACX file
Controlling the Loop

Obtaining Connection Information
Controlling Database information
System
Connection
AS400
OASISDB02
AS400
OASISDB03
AS400
OASISDB04

CompanyID
KY4
IND
MA1
Active
Y
Y
Y
-SET &TAB_NAM6=&SYS_CONN || '/MSCMPP';
System
HP-UX
HP-UX
HP-UX
Connection
ALEDX
ALQDX
AMADX
CompanyID
Active
Y
Y
Y
-SET &CONN_NAME=&SYS_CONN;
The Loop
Read System
Connections
Is this AS400?
?
No
Yes
Is this HP-UX?
?
Yes
Process AS400
No
Process HP-UX
?
Issue Message
Are there more system connections?
Yes
No
Exit
Reading the System Connections
EX PULL_ACTIVE_DBS_FROM_PHARMACY DM_FOLDER=&DM_FOLDER
-SET &NUM_CONNECTIONS = &LINES;
-IF &NUM_CONNECTIONS = 0 THEN GOTO NO_CONNS;
-SET &LOOP_CNT = 1;
SQL DELETE FROM &Target_DB
END
-RUN
-START_PROC
-READ SYS_CONNECTIONS NOCLOSE &SYS_NAME.A25.
&SYS_CONN.A20. &CMPY_ID1.A3. &ACTIVE.A1.
Reading the System Connections
EX PULL_ACTIVE_DBS_FROM_PHARMACY DM_FOLDER=&DM_FOLDER
-SET &NUM_CONNECTIONS = &LINES;
Use Focexec to pull the system information
needed:
•Active –vs- in-active
•Hold output as ALPHA
Set Loop control
Reading the System Connections
-IF &NUM_CONNECTIONS = 0 THEN GOTO NO_CONNS;
-SET &LOOP_CNT = 1;
SQL DELETE FROM &Target_DB
END
-RUN
Set up Loop controls
Reading the System Connections
-START_PROC
-READ SYS_CONNECTIONS NOCLOSE &SYS_NAME.A25.
&SYS_CONN.A20. &CMPY_ID1.A3. &ACTIVE.A1.
Read in the information from the ALPHA HOLD file
created using the Focexec above.
Process AS/400
ENGINE SQLEDA SET AS400
Point to AS/400 Sub System
-REMOTE BEGIN
EX WRITE_ACCESS_FILE SYS_CONN=&SYS_CONN
-REMOTE END
Since source systems are on the sub servers we
need to create the ACX files on those sub servers.
EX CMASAP REQ_NAME=AS400_data_flow, CM_ASYNC=OFF,
PARMS="CMPY_ID1=&CMPY_ID1"
Execute the iDM Data Flow to pull the AS/400
data, passing the “company ID” as a parameter.
Parameter as Filter
Process HP-UX
ENGINE SQLEDA SET HP-UX
Point to HP-UX Sub System
-REMOTE BEGIN
EX WRITE_ACCESS_FILE SYS_CONN=&SYS_CONN
-REMOTE END
Since source systems are on the sub servers we
need to create the ACX files on those sub servers.
EX CMASAP REQ_NAME=HP-UX_data_flow, CM_ASYNC=OFF,
PARMS="SYS_CONN=&SYS_CONN"
Execute the iDM Data Flow to pull the HP-UX data,
passing the system connection information.
Parameter as Transformation
Final Step
-INCR_LOOP
-SET &LOOP_CNT = &LOOP_CNT + 1;
-IF &LOOP_CNT LE &NUM_CONNECTIONS THEN GOTO
START_PROC ELSE GOTO DONE;
Increment the Loop counter and test to see if there
are more connections to process.
Questions and Answers