1 / 25

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

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.

amalia
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Dynamically Loop Through Database Connections With DataMigratorPresented 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. Obtaining Connection Information Controlling Database information System ConnectionCompanyIDActiveAS400 OASISDB02 KY4 YAS400 OASISDB03 IND YAS400 OASISDB04 MA1 Y Controlling the Loop -SET &TAB_NAM6=&SYS_CONN || '/MSCMPP'; System ConnectionCompanyIDActive HP-UX ALEDX Y HP-UX ALQDX Y HP-UX AMADX Y -SET &CONN_NAME=&SYS_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_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.

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

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

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

  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

More Related