DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
krishna81



Group memberships:
Premium Members

Joined: 16 May 2006
Posts: 78
Location: USA
Points: 790

Post Posted: Fri Mar 10, 2017 2:55 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Additional info: information Governance catalog import Extended data Sources
Dear DsXchange Members,
I am currently using information server suite 11.5 version and trying to establish lineage between a stored procedure and ETL jobs(Source tables--->Stored procedure --->Staging Table(Loaded by Sp) --->ETL(Reads staging table and loads into target table)---Target table) .

for the above flow i a am trying import SP table definition to see in IGC.I see the options out there in IGC Information Assets -> import extended Data Sources but not sure how to come up with SP in .CSV format though they have given some sample CSV's.
Though I did look at the following link i am still confused how to use the syntax for a stored procedure.
https://www.ibm.com/support/knowledgecenter/en/SSZJPZ_11.5.0/com.ibm.swg.im.iis.mdwb.doc/topics/r_CSVfileFormatExtendedDataSources.html

Could any of you please throw some lights ao any example would be great if you ever done this scenario in past.Thanks in advance!
-krishna

_________________
Datastage User
leandrohmvieira
Participant



Joined: 02 Sep 2015
Posts: 40
Location: Brasilia, Brazil
Points: 407

Post Posted: Mon Mar 13, 2017 9:15 am Reply with quote    Back to top    

Can you paste your CSV file here? So we can take a look for errors

_________________
Leandro Vieira

Kyros Tecnologia- Uberlândia, Brazil
Rate this response:  
Not yet rated
eostic

Premium Poster



Group memberships:
Premium Members

Joined: 17 Oct 2005
Posts: 3743

Points: 29959

Post Posted: Mon Mar 13, 2017 12:07 pm Reply with quote    Back to top    

The example at that page is a bit tricky to follow.... I will try to add a bit more detail and provide you with some direction. Extended Data Sources of the kinds described on that page (Applicat ...

_________________
Ernie Ostic

blogit!
Open IGC is Here!
Rate this response:  
Not yet rated
krishna81



Group memberships:
Premium Members

Joined: 16 May 2006
Posts: 78
Location: USA
Points: 790

Post Posted: Wed Mar 15, 2017 9:05 am Reply with quote    Back to top    

Thank you both for your posts,
The answer form leandrohmvieira question is i am still in the stage to come up with proper syntax as i still in confused state trying to understand how to leverage these options and put them in place.

My understanding based on eostic message is i can still use Extended Data Sources to import Stored Procedure table definition and out of three sample files should i need to use only
StoredProcedureDef_Sample.csv or all three Application_Sample.csv,File_Sample.csv & StoredProcedureDef_Sample.csv?

Let say i have following sample stored procedure
CREATE PROCEDURE GET_TWOSETS_CUSTOMERINFO
(
P_CREDITLINEAMOUNT IN INTEGER,
CURSOR1_ IN OUT TYPES.REF_CURSOR,
CURSOR2_ IN OUT TYPES.REF_CURSOR
)
AS
BEGIN
OPEN CURSOR1_ FOR
SELECT * FROM CROSSSELLCUSTOMERS
WHERE CREDITLINEAMOUNT >= P_CREDITLINEAMOUNT;

OPEN CURSOR2_ FOR
SELECT * FROM CROSSSELLCUSTOMERS
WHERE CARDTYPE = 'Platinum' AND CREDITLINEAMOUNT >= P_CREDITLINEAMOUNT;
END;

if i use *Sample.csv files what are all the below ones i need to use?Thank you in advance and appreciate your time.

APPLICATION_SAMPLE:
+++ Application - begin +++
Name Description
CRM Customer Resource Application System
+++ Application - end +++

+++ Object Type - begin +++
Name Application Description
Customer Record CRM Customer Data Record
+++ Object Type - end +++

+++ Method - begin +++
Name Application ObjectType Description
Read Customer Name CRM Customer Record Read Method Actions
Return Customer Record Data CRM Customer Record Get Method Actions
+++ Method - end +++

+++ Input Parameter - begin +++
Name Application ObjectType Method Description
CustomerID CRM Customer Record Read Customer Name Customer Account ID
Pass CRM Customer Record Read Customer Name Customer Account Password
+++ Input Parameter - end +++

+++ Output Value - begin +++
Name Application ObjectType Method Description
FullName CRM Customer Record Return Customer Record Data Customer Full (First, Middle, Last) Name
MailingAddress CRM Customer Record Return Customer Record Data Customer primary mailing address
+++ Output Value - end +++


STOREDPROCEDUREDEF_SAMPLE:
+++ Stored Procedure Definition - begin +++
Name Description
SP_CustomerDetails Procedure for reading or writing Customer data
+++ Stored Procedure Definition - end +++

+++ In Parameter - begin +++
Name Stored Procedure Definition Description
ID SP_CustomerDetails Customer Record Number
Name SP_CustomerDetails Customer Last Name
+++ In Parameter - end +++

+++ Out Parameter - begin +++
Name Stored Procedure Definition Description
Policy SP_CustomerDetails Customer Policy Number
Balance SP_CustomerDetails Customer Acct Balance
+++ Out Parameter - end +++

+++ InOut Parameter - begin +++
Name Stored Procedure Definition Description
Code SP_CustomerDetails Customer Access Code
Restrict SP_CustomerDetails Customer Security Code
+++ InOut Parameter - end +++

+++ Result Column - begin +++
Name Stored Procedure Definition Description
FullAddress SP_CustomerDetails Customer Mail Address
Contact SP_CustomerDetails Customer Contact Detail
+++ Result Column - end +++

FILE_SAMPLE:
+++ File - begin +++
Name Description
Customer Data Upload Customer Data FTP Upload directory
Inventory Data Production Inventory FTP Upload directory
+++ File - end +++ [/b]

_________________
Datastage User
Rate this response:  
Not yet rated
eostic

Premium Poster



Group memberships:
Premium Members

Joined: 17 Oct 2005
Posts: 3743

Points: 29959

Post Posted: Thu Mar 16, 2017 2:58 am Reply with quote    Back to top    

Assuming you have the syntax correct, and both files import ok, the answer is "use the one that you (and your users) like best". There is no wrong way here--- I think, as many sites often also con ...

_________________
Ernie Ostic

blogit!
Open IGC is Here!
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours