Title: Dynamically Loop Through Database Connections With DataMigrator Presented by Kathy Getz, Omnicare Inc. Lori Pieper, Information Builders Inc.
1Dynamically Loop Through Database Connections
With DataMigrator Presented byKathy Getz,
Omnicare Inc.Lori Pieper, Information Builders
Inc.
2Agenda
- Presenter Introductions
- Omnicare Business Overview
- Omnicare Architecture
- Challenges/Solution
- Questions/Answers
3Omnicare 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 long-term 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.
4iWay Data Migrator Architectural Diagram
5Environmental Challenges
- Combining AS/400 data with HP-UX data
- For reporting from Windows
- Metadata Table names shared
- 16 AS/400 database
- 64 HP-UX Schemas
- Standardized Metadata across platforms
6Metadata Names by Default
AS/400 Metadata
HP-UX Metadata
7Metadata Names Shared
- Standardized naming conventions
- User Friendly Metadata
8Available 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
9Solution Details
- Dynamically Create Access files (.ACX)AS/400
change table names dynamically
10Solution Details
- Dynamically Create Access files (.ACX)HP-UX
change connection dynamically
11Dynamically Creating Access Files
- Controlling program obtains system connection
information - AS/400
- Changing Tablename Parameter
- HP-UX
- Changing Connection Parameter
12Setting owner name value
Setting table name value
Required file def for .ACX file
Required
Writing .ACX file
13Setting connection value
Required file def for .ACX file
Required
Writing .ACX file
14Controlling the Loop
- Obtaining Connection Information
- Controlling Database information
- System Connection CompanyID ActiveAS400
OASISDB02 KY4
YAS400 OASISDB03
IND YAS400
OASISDB04 MA1
Y
-SET TAB_NAM6SYS_CONN '/MSCMPP'
System Connection CompanyID Active HP-UX
ALEDX
Y HP-UX
ALQDX
Y HP-UX AMADX Y
-SET CONN_NAMESYS_CONN
15The Loop
Read System Connections
Is this AS400?
?
No
Yes
Is this HP-UX?
?
Yes
No
Process AS400
Process HP-UX
Issue Message
?
Are there more system connections?
Yes
No
Exit
16Reading the System Connections
- EX PULL_ACTIVE_DBS_FROM_PHARMACY
DM_FOLDERDM_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.
17Reading the System Connections
- EX PULL_ACTIVE_DBS_FROM_PHARMACY
DM_FOLDERDM_FOLDER -
- -SET NUM_CONNECTIONS LINES
-
- Use Focexec to pull the system information
needed - Active vs- in-active
- Hold output as ALPHA
- Set Loop control
18Reading 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
19Reading 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.
20Process AS/400
Point to AS/400 Sub System
- -REMOTE BEGIN
- EX WRITE_ACCESS_FILE SYS_CONNSYS_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_NAMEAS400_data_flow, CM_ASYNCOFF,
PARMS"CMPY_ID1CMPY_ID1"
Execute the iDM Data Flow to pull the AS/400
data, passing the company ID as a parameter.
21Parameter as Filter
22Process HP-UX
Point to HP-UX Sub System
- -REMOTE BEGIN
- EX WRITE_ACCESS_FILE SYS_CONNSYS_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_NAMEHP-UX_data_flow, CM_ASYNCOFF,
PARMS"SYS_CONNSYS_CONN"
Execute the iDM Data Flow to pull the HP-UX data,
passing the system connection information.
23Parameter as Transformation
24Final 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.
25Questions and Answers