Skip to Content
SAP IQ

Setting Remote Procedure Call

Tags:

This doc talk about how to set up RPC to other server, when RPC is  embedded with in begin … end block.   When Remote procedure is
created, engine has no knowledge of what Remote Procedure does and therefore It cannot implicitly define result set for Remote Procedure.

  If Remote Procedure is executed using  "call foo(...)" in DBISQL or dbisqlc, the application will open a cursor on the call statement
and the proper "cursor describe stuff" will automatically happen. BUT, if you attempt to execute a "call foo(...)" within a procedure
or batch, the engine does not automatically open a cursor on the call. Instead, it is up to the user to explicitly force a cursor to be opened either by
declaring a cursor within the stored procedure or batch or using the RPC in the from clause of a select statement. Furthermore, if the RPC is going

to be used in the from clause, then the RPC *MUST* be created with a RESULT clause in order to allow the engine to predefine the schema

of the expected result set.

STEPS:

Add source and target SAP Sybase IQ server entries in the interfaces. Add $ODBCINI and $ODBCHOME environment variables in $SYBASE/IQ-15_4/IQ-15_4.csh  and then source to setup environment variables

  1. Create remote server

  CREATE SERVER "iqdemo1543" CLASS 'SAODBC' USING 'dsn=iqdemo1543';

   2. Create  Externlogin

       CREATE EXTERNLOGIN "DBA" TO "iqdemo1543" REMOTE LOGIN "DBA" IDENTIFIED BY 'sql';

   3. Create Remote Procedure:

   create "DBA"."remwho"()

       result(

      ConnHandle unsigned bigint,

     IQconnID unsigned bigint,
 
     Userid varchar(255),

    ReqType varchar(255),

    IQCmdType char(32),

    BlockedOn unsigned bigint,

    BlockUserid varchar(255),

   IQCursors unsigned integer,

   IQThreads unsigned integer,

   IQIdle integer,

   SAIdle integer,

   TempTableSpaceKB unsigned bigint,

   TempWorkSpaceKB unsigned bigint )

   at 'iqdemo1543...sp_iqwho'

   4. Execute Remote Procedure


begin select * from remwho() end

ConnHandle           IQconnID             Userid
                                                        
ReqType

IQCmdType                        BlockedOn            BlockUserid



                                                                IQCursors

IQThreads  IQIdle      SAIdle      TempTableSpaceKB     TempWorkSpaceKB

--------------------------------------------------------------------------------

-----------------------------------------------------------------------

23                   5625                 DBA
                                                        
OPEN

IQUTILITYOPENCURSOR              0                    (NULL)
                                                             
0

1

      0           0           0                    0



(1 rows)


Execution time: 0.025 seconds

No comments