DSXchange

Hi All,
How do I execute Stored Procedure in Sybase from DS PX Jobs.

Thanks
SCK

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

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

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.