Dynamically Loop Through Database Connections With DataMigrator Presented by Kathy Getz, Omnicare Inc. Lori Pieper, Information Builders Inc.

1
Dynamically Loop Through Database Connections
With DataMigrator Presented byKathy Getz,
Omnicare Inc.Lori Pieper, Information Builders
Inc.
2
Agenda

• Presenter Introductions
• Omnicare Business Overview
• Omnicare Architecture
• Challenges/Solution
• Questions/Answers

3
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 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.

4
iWay Data Migrator Architectural Diagram
5
Environmental 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

6
Metadata Names by Default
AS/400 Metadata
HP-UX Metadata
7
Metadata Names Shared

• Standardized naming conventions
• User Friendly Metadata

8
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
9
Solution Details

• Dynamically Create Access files (.ACX)AS/400
  change table names dynamically

10
Solution Details

• Dynamically Create Access files (.ACX)HP-UX
  change connection dynamically

11
Dynamically Creating Access Files

• Controlling program obtains system connection
  information
• AS/400
• Changing Tablename Parameter
• HP-UX
• Changing Connection Parameter

12
Setting owner name value
Setting table name value
Required file def for .ACX file
Required
Writing .ACX file
13
Setting connection value
Required file def for .ACX file
Required
Writing .ACX file
14
Controlling 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
15
The 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
16
Reading 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.
17
Reading 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

18
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
19
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.
20
Process AS/400

• ENGINE SQLEDA SET AS400

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.
21
Parameter as Filter
22
Process HP-UX

• ENGINE SQLEDA SET 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.
23
Parameter as Transformation
24
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.
25
Questions and Answers