Hi All,
How do I execute Stored Procedure in Sybase from DS PX Jobs.
Thanks
SCK
Execute Stored Procedures in Sybase for PX
Moderators: chulett, rschirm, roy
Hi SCK,
In DS 7.5 there is a Stored Procedure stage which can be used to execute stored procedures. If you are on a lower version you can probably execute the stored procedure through some work around but Iam sure that it will not return the output parameters.
HTH
--Rich
Pride comes before a fall
Humility comes before honour
In DS 7.5 there is a Stored Procedure stage which can be used to execute stored procedures. If you are on a lower version you can probably execute the stored procedure through some work around but Iam sure that it will not return the output parameters.
HTH
--Rich
Pride comes before a fall
Humility comes before honour
Hi SCK,
Using Stored procedure stage you can achieve this or create following server or parallel user defined routine through Datastage manager ,then call that routine in Before / After routine job parameter screen.
[b]Ex: calling routine [/b]
Before-Job subroutine: Input value:
SYBASESQL Exec procedure name
SYBASESQL is user defined routine name.
SYBASESQL is user defined routine name.
Following is routine script for SYBASESQL
/ *
$INCLUDE UNIVERSE.INCLUDE ODBC.H
$INCLUDE DSINCLUDE JOBCONTROL.H
* Setup ODBC DSN and UserID and Password
strDataSource = DSGetParamInfo(DSJ.ME,"DSN", DSJ.PARAMVALUE)
strODBCUserID = DSGetParamInfo(DSJ.ME,"USERID", DSJ.PARAMVALUE)
strODBCPassword = DSGetParamInfo(DSJ.ME,"PWD", DSJ.PARAMVALUE)
* InputArg is a string of the form ODBCStageName;SQLStatement
* Initialise BCI handles
henv = SQL.NULL.HENV
hdbc = SQL.NULL.HDBC
hstmt = SQL.NULL.HSTMT
hstmt1 = SQL.NULL.HSTMT
Connected = @FALSE
henv2 = SQL.NULL.HENV
hdbc2 = SQL.NULL.HDBC
hstmt2 = SQL.NULL.HSTMT
Connected2 = @FALSE
* Establish First connection to the DataSource
Ret = SQLAllocEnv(henv)
Function = 'SQLAllocEnv'
GoSub ErrHandler
Ret = SQLAllocConnect(henv,hdbc)
Function = 'SQLAllocConnect'
GoSub ErrHandler
Ret = SQLConnect(hdbc,strDataSource,strODBCUserID,strODBCPassword)
Function = 'SQLConnect'
GoSub ErrHandler
Connected = @TRUE
Ret = SQLSetConnectOption(hdbc, SQL.TX.PRIVATE, SQL.TX.PRIVATE.ON)
Function = 'SQLSetConnectOption(SQL.TX.PRIVATE)'
GoSub ErrHandler
Ret = SQLAllocStmt(hdbc, hstmt1)
Function = 'SQLAllocStmt'
GoSub ErrHandler
* Get SQL Statement to execute
SQLStatement1 = InputArg
* Call DSLogWarn(InputArg, "sybsql")
* Execute the first statement
Ret = SQLExecDirect(hstmt1, SQLStatement1)
Function = 'SQLExecDirect'
hstmt = hstmt1
GoSub ErrHandler
* Close Connection One now that we have finished with it
Ret = SQLTransact(henv,hdbc,SQL.COMMIT)
Function = 'SQLTransact'
hstmt = SQL.NULL.HSTMT
GoSub ErrHandler
* Turn of private transaction otherwise we can't disconnect without an error
Ret = SQLSetConnectOption(hdbc,SQL.TX.PRIVATE,SQL.TX.PRIVATE.OFF)
Function = "SQLSetConnectOption(SQL.PRIVATE.TX)"
GoSub ErrHandler
* Free any resources used by this subroutine
Ret = SQLFreeStmt(hstmt1, SQL.DROP)
Function = 'SQLFreeStmt'
hstmt = hstmt1
GoSub ErrHandler
hstmt1 = SQL.NULL.HSTMT
hstmt = SQL.NULL.HSTMT
Ret = SQLDisconnect(hdbc)
Function = 'SQLDisconnect'
GoSub ErrHandler
Connected = @FALSE
Ret = SQLFreeConnect(hdbc)
Function = 'SQLFreeConnect'
GoSub ErrHandler
hdbc = SQL.NULL.HDBC
Ret = SQLFreeEnv(henv)
Function = 'SQLFreeEnv'
GoSub ErrHandler
henv = SQL.NULL.HENV
NormalExit:
Ans = 0 ; * set this to non-zero to stop the stage/job
RETURN
ErrorExit:
Ans = 1
RETURN
ErrHandler:
BEGIN CASE
CASE Ret EQ SQL.SUCCESS OR Ret EQ SQL.NO.DATA.FOUND
NULL
CASE Ret EQ SQL.SUCCESS.WITH.INFO
InfoText = "ExecSQL call to ":Function:" returned informational message."
Call DSLogWarn(InfoText, "True")
Loop
Ret = SQLError(henv,hdbc,hstmt,sqlstate,dbms.code,errmsg)
Until Ret NE SQL.SUCCESS
InfoText := "<L>sqlstate=":sqlstate:", dbms.code=":dbms.code:"<L>":errmsg
Repeat
InfoText = Ereplace(InfoText,"<L>",CHAR(13):CHAR(10))
Print InfoText
CASE @TRUE
Call DSLogWarn(ErrorText, "False")
ErrorText = "True Loop":Function:" failed."
* Call DSLogWarn(ErrorText, "MyCopyOfSybsql")
*Loop
* Ret = SQLError(henv,hdbc,hstmt,sqlstate,dbms.code,errmsg)
*Until Ret NE SQL.SUCCESS
* ErrorText := "<L>sqlstate=":sqlstate:", dbms.code=":dbms.code:"<L>":errmsg
*Repeat
*ErrorText = Ereplace(ErrorText,"<L>",CHAR(13):CHAR(10))
*Print ErrorText
*If NOT(NUM(hstmt1)) Then Ignore = SQLFreeStmt(hstmt1,SQL.DROP)
*If NOT(NUM(hstmt2)) Then Ignore = SQLFreeStmt(hstmt2,SQL.DROP)
*If Connected Then
* * Turn of private transaction otherwise we can't disconnect without an error
* Ignore = SQLSetConnectOption(hdbc,SQL.TX.PRIVATE,SQL.TX.PRIVATE.OFF)
* Ignore = SQLDisconnect(hdbc)
*End
*If NOT(NUM(hdbc)) Then Ignore = SQLFreeConnect(hdbc)
*If NOT(NUM(henv)) Then Ignore = SQLFreeEnv(henv)
Ans = 1
ErrorCode = 1
Call DSLogFatal("SQL Failed",sybsql)
END CASE
ErrorCode = 0 ;* set this to non-zero to stop the stage/job
//.........................................................................................................
Please try this.
Thanks
Man
Using Stored procedure stage you can achieve this or create following server or parallel user defined routine through Datastage manager ,then call that routine in Before / After routine job parameter screen.
[b]Ex: calling routine [/b]
Before-Job subroutine: Input value:
SYBASESQL Exec procedure name
SYBASESQL is user defined routine name.
SYBASESQL is user defined routine name.
Following is routine script for SYBASESQL
/ *
$INCLUDE UNIVERSE.INCLUDE ODBC.H
$INCLUDE DSINCLUDE JOBCONTROL.H
* Setup ODBC DSN and UserID and Password
strDataSource = DSGetParamInfo(DSJ.ME,"DSN", DSJ.PARAMVALUE)
strODBCUserID = DSGetParamInfo(DSJ.ME,"USERID", DSJ.PARAMVALUE)
strODBCPassword = DSGetParamInfo(DSJ.ME,"PWD", DSJ.PARAMVALUE)
* InputArg is a string of the form ODBCStageName;SQLStatement
* Initialise BCI handles
henv = SQL.NULL.HENV
hdbc = SQL.NULL.HDBC
hstmt = SQL.NULL.HSTMT
hstmt1 = SQL.NULL.HSTMT
Connected = @FALSE
henv2 = SQL.NULL.HENV
hdbc2 = SQL.NULL.HDBC
hstmt2 = SQL.NULL.HSTMT
Connected2 = @FALSE
* Establish First connection to the DataSource
Ret = SQLAllocEnv(henv)
Function = 'SQLAllocEnv'
GoSub ErrHandler
Ret = SQLAllocConnect(henv,hdbc)
Function = 'SQLAllocConnect'
GoSub ErrHandler
Ret = SQLConnect(hdbc,strDataSource,strODBCUserID,strODBCPassword)
Function = 'SQLConnect'
GoSub ErrHandler
Connected = @TRUE
Ret = SQLSetConnectOption(hdbc, SQL.TX.PRIVATE, SQL.TX.PRIVATE.ON)
Function = 'SQLSetConnectOption(SQL.TX.PRIVATE)'
GoSub ErrHandler
Ret = SQLAllocStmt(hdbc, hstmt1)
Function = 'SQLAllocStmt'
GoSub ErrHandler
* Get SQL Statement to execute
SQLStatement1 = InputArg
* Call DSLogWarn(InputArg, "sybsql")
* Execute the first statement
Ret = SQLExecDirect(hstmt1, SQLStatement1)
Function = 'SQLExecDirect'
hstmt = hstmt1
GoSub ErrHandler
* Close Connection One now that we have finished with it
Ret = SQLTransact(henv,hdbc,SQL.COMMIT)
Function = 'SQLTransact'
hstmt = SQL.NULL.HSTMT
GoSub ErrHandler
* Turn of private transaction otherwise we can't disconnect without an error
Ret = SQLSetConnectOption(hdbc,SQL.TX.PRIVATE,SQL.TX.PRIVATE.OFF)
Function = "SQLSetConnectOption(SQL.PRIVATE.TX)"
GoSub ErrHandler
* Free any resources used by this subroutine
Ret = SQLFreeStmt(hstmt1, SQL.DROP)
Function = 'SQLFreeStmt'
hstmt = hstmt1
GoSub ErrHandler
hstmt1 = SQL.NULL.HSTMT
hstmt = SQL.NULL.HSTMT
Ret = SQLDisconnect(hdbc)
Function = 'SQLDisconnect'
GoSub ErrHandler
Connected = @FALSE
Ret = SQLFreeConnect(hdbc)
Function = 'SQLFreeConnect'
GoSub ErrHandler
hdbc = SQL.NULL.HDBC
Ret = SQLFreeEnv(henv)
Function = 'SQLFreeEnv'
GoSub ErrHandler
henv = SQL.NULL.HENV
NormalExit:
Ans = 0 ; * set this to non-zero to stop the stage/job
RETURN
ErrorExit:
Ans = 1
RETURN
ErrHandler:
BEGIN CASE
CASE Ret EQ SQL.SUCCESS OR Ret EQ SQL.NO.DATA.FOUND
NULL
CASE Ret EQ SQL.SUCCESS.WITH.INFO
InfoText = "ExecSQL call to ":Function:" returned informational message."
Call DSLogWarn(InfoText, "True")
Loop
Ret = SQLError(henv,hdbc,hstmt,sqlstate,dbms.code,errmsg)
Until Ret NE SQL.SUCCESS
InfoText := "<L>sqlstate=":sqlstate:", dbms.code=":dbms.code:"<L>":errmsg
Repeat
InfoText = Ereplace(InfoText,"<L>",CHAR(13):CHAR(10))
Print InfoText
CASE @TRUE
Call DSLogWarn(ErrorText, "False")
ErrorText = "True Loop":Function:" failed."
* Call DSLogWarn(ErrorText, "MyCopyOfSybsql")
*Loop
* Ret = SQLError(henv,hdbc,hstmt,sqlstate,dbms.code,errmsg)
*Until Ret NE SQL.SUCCESS
* ErrorText := "<L>sqlstate=":sqlstate:", dbms.code=":dbms.code:"<L>":errmsg
*Repeat
*ErrorText = Ereplace(ErrorText,"<L>",CHAR(13):CHAR(10))
*Print ErrorText
*If NOT(NUM(hstmt1)) Then Ignore = SQLFreeStmt(hstmt1,SQL.DROP)
*If NOT(NUM(hstmt2)) Then Ignore = SQLFreeStmt(hstmt2,SQL.DROP)
*If Connected Then
* * Turn of private transaction otherwise we can't disconnect without an error
* Ignore = SQLSetConnectOption(hdbc,SQL.TX.PRIVATE,SQL.TX.PRIVATE.OFF)
* Ignore = SQLDisconnect(hdbc)
*End
*If NOT(NUM(hdbc)) Then Ignore = SQLFreeConnect(hdbc)
*If NOT(NUM(henv)) Then Ignore = SQLFreeEnv(henv)
Ans = 1
ErrorCode = 1
Call DSLogFatal("SQL Failed",sybsql)
END CASE
ErrorCode = 0 ;* set this to non-zero to stop the stage/job
//.........................................................................................................
Please try this.
Thanks
Man
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This "solution" will only work for 30 days.
Then you will have to buy a licence for the ODBC driver, or for some other third party UNIX-based ODBC driver that you elect to purchase.
The branded ODBC drivers that ship with DataStage are licensed only for use with DataStage stages, not for use with BCI functions.
Then you will have to buy a licence for the ODBC driver, or for some other third party UNIX-based ODBC driver that you elect to purchase.
The branded ODBC drivers that ship with DataStage are licensed only for use with DataStage stages, not for use with BCI functions.
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.