Input to a stored Procedure
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
Input to a stored Procedure
Hi
I it possible to give an sequnetial file as input to a stored procedure.I want to pass file data as input to the procedure parameters .
If it is possible then what will be the syntax in stored procedure
Thanks
Arif
I it possible to give an sequnetial file as input to a stored procedure.I want to pass file data as input to the procedure parameters .
If it is possible then what will be the syntax in stored procedure
Thanks
Arif
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your job will parse each line from the sequential file into a row (a set of columns). These can then be transformed/enriched within the DataStage job, which continues to pass rows through the job design. When you invoke your stored procedure, you do so once for each row processed, supplying column values from the input row to the stage from which you invoke the SP to replace the stored procedure's arguments.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
Not abel to view content
Hi Ray
I have just bought the membership for charter members ,but still I am not able to view all the contents ,do you have any idea how much time it will take to activate the membership.
Thanks
Arif
I have just bought the membership for charter members ,but still I am not able to view all the contents ,do you have any idea how much time it will take to activate the membership.
Thanks
Arif
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Not abel to view content
I thought it was immediate. Contact either the Editor or the Webmaster (choose Contact from the menu at top). You may, however, need to re-connect to pick up the new setting.mab_arif16 wrote:Hi Ray
I have just bought the membership for charter members ,but still I am not able to view all the contents ,do you have any idea how much time it will take to activate the membership.
Thanks
Arif
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
Syntax for input column
Hi Ray
If the input columns are col1,col2 ...etc. from input files , and the parameters to stored procedure are @ input1,@input2..etc
In the syntax to call the procedure
Will the syntax of stored procedure be #procedurename#@input1=col1,@input2=col2 or
#procedurename#@input1=DSLink.col1,@input2=DSLink.col2
OR in the parameters tab of SP stage we have to map the output column name to the parameters name .
If the input columns are col1,col2 ...etc. from input files , and the parameters to stored procedure are @ input1,@input2..etc
In the syntax to call the procedure
Will the syntax of stored procedure be #procedurename#@input1=col1,@input2=col2 or
#procedurename#@input1=DSLink.col1,@input2=DSLink.col2
OR in the parameters tab of SP stage we have to map the output column name to the parameters name .
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
Re: Syntax for input column
In the SP stage you will have to map the column. The input columns like as mentioned will be parsed to the input columns of your SP.mab_arif16 wrote: Will the syntax of stored procedure be #procedurename#@input1=col1,@input2=col2 or
#procedurename#@input1=DSLink.col1,@input2=DSLink.col2
OR in the parameters tab of SP stage we have to map the output column name to the parameters name .
The tabs in the stage explicitly allow you to enter them.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 87
- Joined: Sat Mar 18, 2006 11:45 pm
Procedure call syntax , map to column in Stored procedure
Hi guru
I have tried to give the input column name in maps to column in parameter tab,but I am getting error ,is there some changes needed to be done to done to procedure call syntax in the SP stage as well.
I have tried to give the input column name in maps to column in parameter tab,but I am getting error ,is there some changes needed to be done to done to procedure call syntax in the SP stage as well.
In the stored procedure stage under the
Stage tab - > Parameters:
Enter the all the parameters. Meaning that all your inputs to the Stored Procedure and output to the stored procedure. Give the type of each one accordingly ie; input,output,function.. .
Stage tab -> Syntax
Give the procedure name
for eg: #pDbSchema#.dstage_batch_activate.activateBatch
and check mark Generate Procedure call.
You will see the generated syntax of the call statment made by the stage.
Then under the Input Tab -> General:
Check mark Execute procdure for each row and set transaction size as required.
Then goto the Input tab -> Column
Your input column should appear there.
Do the similar settings for the Output tab.
If your procedure is returning multiple rows then you need to give the End of Data code that your procedure will return.
Stage tab - > Parameters:
Enter the all the parameters. Meaning that all your inputs to the Stored Procedure and output to the stored procedure. Give the type of each one accordingly ie; input,output,function.. .
Stage tab -> Syntax
Give the procedure name
for eg: #pDbSchema#.dstage_batch_activate.activateBatch
and check mark Generate Procedure call.
You will see the generated syntax of the call statment made by the stage.
Then under the Input Tab -> General:
Check mark Execute procdure for each row and set transaction size as required.
Then goto the Input tab -> Column
Your input column should appear there.
Do the similar settings for the Output tab.
If your procedure is returning multiple rows then you need to give the End of Data code that your procedure will return.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.