New to DataStage and need advice on a massive file layout
Posted: Thu Mar 08, 2018 6:59 am
I am very new to DataStage and have a request that I'm not even sure is possible or not in DataStage. I'm hoping someone can give me some general advice to point me in the right direction.
I have been tasked with creating a file extract from an Oracle based system. This extract has a file layout that is 180 printed pages long. The extract is a completely custom format that has a number of groups and sub-groups. For example, some of the record layout is as follows:
PATIENT_VISIT (will be thousands of records per run)
-> PATIENT_INFORMATION (e.g. name, address, SSN, etc.)
-> PROCEDURE_HEADER
-> PROCEDURE (can be many of these)
-> PROCEDURE_STAFF (will be multiple per procedure)
-> BILLING_HEADER
-> CHARGES (can be many)
etc...
At any rate, this request is a beast and I've already written it on another platform but need to convert it to DataStage. There are roughly 60-80 different Oracle tables being used in about 20-30 separate SQL statements that all need to be put together into this massive format.
Some of my questions are:
1. Is this even possible in DataStage?
2. If it is possible, would a Parallel or Server job be better suited?
3. I've been told that developing it all in BASIC in a Server job may work but I don't know how to connect to Oracle from BASIC. Is this even possible?
Being new to DataStage I may not even know if I'm asking the right questions here or not so if I'm not, I'd appreciate being pointed in the right direction.
Thanks,
John
I have been tasked with creating a file extract from an Oracle based system. This extract has a file layout that is 180 printed pages long. The extract is a completely custom format that has a number of groups and sub-groups. For example, some of the record layout is as follows:
PATIENT_VISIT (will be thousands of records per run)
-> PATIENT_INFORMATION (e.g. name, address, SSN, etc.)
-> PROCEDURE_HEADER
-> PROCEDURE (can be many of these)
-> PROCEDURE_STAFF (will be multiple per procedure)
-> BILLING_HEADER
-> CHARGES (can be many)
etc...
At any rate, this request is a beast and I've already written it on another platform but need to convert it to DataStage. There are roughly 60-80 different Oracle tables being used in about 20-30 separate SQL statements that all need to be put together into this massive format.
Some of my questions are:
1. Is this even possible in DataStage?
2. If it is possible, would a Parallel or Server job be better suited?
3. I've been told that developing it all in BASIC in a Server job may work but I don't know how to connect to Oracle from BASIC. Is this even possible?
Being new to DataStage I may not even know if I'm asking the right questions here or not so if I'm not, I'd appreciate being pointed in the right direction.
Thanks,
John