Basics on stored procedures

[satya]

Re: Basics on stored procedures

Apologize for the delay.got hung up @ some other work .btw the calling program is in SQLRPGLE

[B.Hauser]

Re: Basics on stored procedures

Just have a look at the following article, in which a result set returned by a stored procedure is consumed with RPG.
DB2 for i: Process Stored Procedure Result Sets as Cursors

Birgitta

[satya]

Re: Basics on stored procedures

Just have a look at the following article, in which a result set returned by a stored procedure is consumed with RPG.
DB2 for i: Process Stored Procedure Result Sets as Cursors

Birgitta

Hello Hauser,
This article refers to returning cursor as a result set and using it in the calling program.I was refeerring to as how to return array as result set and use it in an sqlrpgle program.

Got it i was wrong in the way i was fetching the data in the calling program.
i used a direct fetch instead of fetching for a specified rows

fetch into :ds; this was wrong

instead i had to use fetch c1 for :rows rows into :ds.

thanks hauser i got the answer here
http://www.code400.com/forum/showthr...data-structure

[satya]

Re: Basics on stored procedures

Hello everyone,

I am back with couple of question on UDTF

DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURNEDTABLE
(RRN INTEGER, USER CHAR(10), PGMN CHAR(10));
insert into session.RETURNEDTABLE ( select rrn(T1), CUSR,
CPGMN FROM T1 WHERE CUSR <> ' ');
RETURN
SELECT RRN,USER,PGMN FROM session.RETURNEDTABLE;

Having an issue with user field when inserting some of the values
Distinct value of user are
'abcdef'
*abcdefgh'
'def'

whenever the function encounters a record with user name as '*abcdefgh',it ends with sqlstate 22001 in db2(value too long for variable).

I have defined length and type for the variables users in both the table and temp table same.
It inserts the records with the value 'abcdef' and stops at the record where it's finding this '*' record.What am i doing wrong.
i have tried varchar data type ,doesn't work.

[Michael Jones]

Re: Basics on stored procedures

Hi Satya,

USER is a reserved word, and is used to access the special register containing the run-time authorization ID (current user) on the server for the job. It is a VARCHAR(18). So, it is trying to stick 18 bytes into a column defined to hold 10.

I recommend changing the name of column USER to USER_ID, and potentially increasing it from CHAR(10) to CHAR(18) or VARCHAR(18) - if your intent was to access the current user in special register USER.

The failure is likely taking place at that last SELECT, because it is fetching special register USER as VARCHAR(18), and I'm guessing your function is defined to return a CHAR(10) in column position 2.

[satya]

Re: Basics on stored procedures

Hi Satya,

USER is a reserved word, and is used to access the special register containing the run-time authorization ID (current user) on the server for the job. It is a VARCHAR(18). So, it is trying to stick 18 bytes into a column defined to hold 10.

I recommend changing the name of column USER to USER_ID, and potentially increasing it from CHAR(10) to CHAR(18) or VARCHAR(18) - if your intent was to access the current user in special register USER.

The failure is likely taking place at that last SELECT, because it is fetching special register USER as VARCHAR(18), and I'm guessing your function is defined to return a CHAR(10) in column position 2.

thanks micheal that was the issue.

[satya]

Re: Basics on stored procedures

hello everyone ,

i am upto practising a external udtf i am follwing scott's pdf for this.i have successfully created the function and program.but the program loops even after the return statement is executed.
please find the code below

create function extudtf()
returns table(init char(4),numb dec(6,0))
specific abc/extudtf
external name abc/extudtf1
language rpgle
parameter style db2sql
contains sql
disallow parallel;

my program

dextudtf pr extpgm('EXTUDTF1')
d init 4a
d numb 6p 0
d indini 5i 0
d indnum 5i 0
d sqlst 5a
d func 10a
d specificn 30a varying
d errmsg 50a varying
d scratchpad 20a
d calltype 3i 0
dextudtf pi
d init 4a
d numb 6p 0
d indini 5i 0
d indnum 5i 0
d sqlst 5a
d func 10a
d specificn 30a varying
d errmsg 50a varying
d scratchpad 20a
d calltype 3i 0
d ds1 ds qualified
d init 4a
d numb 6p 0
/free
exec sql
declare c1 cursor for
select carinit,carnumb from cars;
exec sql
close c1;
exec sql
open c1;
dow sqlcod = 0;
exec sql
fetch from c1 into :ds1;
enddo;

if sqlcode =100;
scratchpad =init;
*inlr=*on;
endif;

exec sql
close c1;

return;
/end-free

i wanted to experiment on scratchpad basically as to what its real time use can be.Anybody ever use this paramter in real time ?

[B.Hauser]

Re: Basics on stored procedures

I can't imagine, you followed Scotts instructions!

1. The parameter definition for the DB2 SQL Style is not correct, i.e. the length definition of most of the parameter fields is wrong (for example: function 517 varying versus 10A).
2. An UDTF does not make any sense in your example, because you use embedded SQL to read something in a loop (why not defining and reading the SQL-Statement directly. But for a test, may be.
External UDTFs are great for accessing non database objects, such as data areas, user spaces or IFS files.
3. An UDTF is normally based on a CALLBACK processing method for returning multiple rows (that's why it has to be specified within the FROM clause).
An (external) UDTF i.e. the RPG function/program is called at least 3 times from the database manager:
- Once with the call type -1 (= Open Call)
- One or multiple times with the call type 0 (= Fetch Call)
- Once at the end with call type 1 (= Close Call)
Those call types are not checked are not checked in your program.
To get information about the last record, the SQLState Parameter must be set to '02000', otherwise, the database manager will continue to call your program and never gets the information to perform the close call.
4. A RPG program that is registered as UDTF should never be ended with *LR=On.

Scratchpath can be used to preserve information between the different calls (for example you may summarize something and with eacht fetch you'll get additional information that has to be added.
If the program is not ended with *LR=*ON, it is also possible to store those information in global variables.

For more information about UDTFs you may also read the following article:
The Power of User Defined Functions

Birgitta

[satya]

Re: Basics on stored procedures

Hi folks ,i am doing a recap of all the learnings i have had on stored procedures and functions,

i was confused and couple of question came into my mind.

1.what's done(behind the screen) when a stored procedure is created(compiled). I read somewhere it creates a permanent access plan.
But then i was confused with ,when we run a simple query it creates a access plan and when we run the same query again the process is faster since access plan is already present. if i run the same query in a stored proc its not useful.

i know there are other uses of stored procedure like branching,looping etc.. and wring multiple sql ddl,dml statements.but does it really has to with creating a accees plan?


2.I read stored procedure stored in database catalog (basically some where in the memory) .How do i access the memory where the procedure is stored.

if i have rpgle external stored procedure compiled with no source ,is there a possible way to see and edit the code by accesing the address.

3.I have read about the security features of stored procedures but couldn't understand it clearly.say dbadmin can access the object where a user cannot din't get the part.

4. It is said that when using a normal call to the database using individaual update select,insert it takes lot of time,since its a two way communication.because the whole statement is passed on the communication medium(what does this means) and how does it slows down the performance.

5.where as call to a stored procedure includes only a call and parameters. (Its hard to understand if a whole sql statement vs a parameters (passing) can affect the performance so much.)

Have lot more questions.. will ask em later

and thanks birgitta for the answers .never knew about the callback processing,tht was new.

[Michael Jones]

Re: Basics on stored procedures

1.what's done(behind the screen) when a stored procedure is created(compiled). I read somewhere it creates a permanent access plan.
But then i was confused with ,when we run a simple query it creates a access plan and when we run the same query again the process is faster since access plan is already present. if i run the same query in a stored proc its not useful.

i know there are other uses of stored procedure like branching,looping etc.. and wring multiple sql ddl,dml statements.but does it really has to with creating a accees plan?

• SQL stored procedures: compile generates C language source and then compiles it to a *PGM object if PROGRAM TYPE MAIN was specified, or a*SRVPGM object if PROGRAM TYPE SUB was specified. Whether or not access plan(s) gets embedded into the compiled object, depends on whether or not dynamic or static SQL was used. When the procedure is run, if static SQL was used, access plan(s), one for each static SQL statement, will get embedded into the compiled object. For dynamic SQL, access plans are calculated each time the SQL statement is run. A row will be created in QSYS2.SYSPROCS for each SQL stored procedure created.
• External stored procedures: Example = RPG *PGM or *SRVPGM procedure acting as a stored procedure. When you compile an SQL external stored procedure, for example pointing to an RPG *PGM or *SRVPGM procedure, only a row in QSYS2.SYSPROCS is created. That row serves as a simple middleman to provide enough information for the SQL engine to be able to call the external object and pass it parameters.
• Access plans in general: This is a complex subject, and the information that follows is a dramatic over-simplification. Calculating access plans is fairly expensive, so you want to avoid access plan recalculation when possible. Static SQL: an access plan will get calculated and stored into the compiled object the first time it is run (the *PGM or *SRVPGM object will grow in size as a result of inserting the access plan). Typically, an access plan for static SQL will get reused for subsequent invocations. However, if run time environmental factors have changed significantly since the access plan was last calculated, the system may recalculate and re-save the access plan to re-optimize the statement to the current run time environment. Dynamic SQL: If you run the same dynamic SQL multiple times in a row, some of the data you're querying is likely cached in memory, and you'll gain speed simply because of that. If the dynamic SQL executed multiple times in a row is EXACTLY the same, my understanding is the access plan will get reused instead of recalculated. Typically though, inside programs, dynamic SQL executed multiple times in a row, will not be exactly the same, so an access plan will get re-calculated (with some exceptions) for dynamic SQL with even minor differences from prior executions. Using SQL PREPARE with parameter markers can allow the reuse of an access plan. SQL package (*SQLPKG) objects can reduce the need for the system to re-calculate an access plan.

2.I read stored procedure stored in database catalog (basically some where in the memory) .How do i access the memory where the procedure is stored.

if i have rpgle external stored procedure compiled with no source ,is there a possible way to see and edit the code by accesing the address.

QSYS2.SYSPROCS will contain a row for each SQL or RPGLE external stored procedure. To see and edit the code, you must locate the SQL or RPGLE code from which the procedure object was compiled. Again, the row in QSYS2.SYSPROCS for an RPGLE external stored procedure is simply a stub that points to the external RPGLE *PGM or *SRVPGM object.

3.I have read about the security features of stored procedures but couldn't understand it clearly.say dbadmin can access the object where a user cannot din't get the part.

To my knowledge, the security behavior of stored procedures will be the same as *PGM or *SRVPGM objects.

4. It is said that when using a normal call to the database using individaual update select,insert it takes lot of time,since its a two way communication.because the whole statement is passed on the communication medium(what does this means) and how does it slows down the performance.

I'm not sure what that means. In general, there is a lot of overhead for each SQL statement fed to the database. If the client is on a remote system and the SQL statement must be transmitted over a network, there is even more overhead. You want to design your applications such that you minimize the number of SQL statements fed to the database. This means using SET based SQL as much as possible. This is in comparison to SQL that processes only a single row at a time (very inefficient compared to SET based SQL).

5.where as call to a stored procedure includes only a call and parameters. (Its hard to understand if a whole sql statement vs a parameters (passing) can affect the performance so much.)

Stored procedures are one way to improve efficiency by pushing more work to server side. You can call a stored procedure with parameters, and that stored procedure can perform a large number of operations (dozens, hundreds, thousands, millions, etc.). When all those operations are complete, control returns to the caller. From an external program viewpoint, only one call to the database was performed (i.e. the stored procedure call). The stored procedure operations will be running server side, without constant contact with the client requester. That is much more efficient than a design where the external program is passing those dozens, hundreds, thousands, millions, etc. of I/O requests to the database one at a time. Another very nice benefit of a stored procedure call, is that the procedure can return multiple result sets to the requester from a single call.

[satya]

Re: Basics on stored procedures

Hello guys,

1. What is the alternative/replacement of sp_helptext storedproc name in db2as400?

2. if i have an stored procedure object and do not have the source (the defination) how will i modify/view it ?

3.Where is the default stored procedure created in as400.in my case it is generated in QGPL(as checked in sysprocs table) library. how do i change it to store it point INto another library.

4. when i try to give a wrkobj stored procedure name or the name i saved it with it doesn't show it up. (i am creating a txt file and writing my stored procedure defination there and then runsqlstm.

[Michael Jones]

Re: Basics on stored procedures

Hi Satya,

Hello guys,

1. What is the alternative/replacement of sp_helptext storedproc name in db2as400?
MJ: GENERATE_SQL stored procedure
You can also locate the object in the IBM i Navigator tool, right click the object, and choose to generate the SQL that way from the pop up menu. Also in Navigator, you can locate the object, right click it, and choose Definition from the pop up menu to get object attributes.

2. if i have an stored procedure object and do not have the source (the defination) how will i modify/view it ?
MJ: Use GENERATE_SQL to fetch the source. Note: when using the GENERATE_SQL stored procedure, the source member into which you're depositing the source must already exist.

3.Where is the default stored procedure created in as400.in my case it is generated in QGPL(as checked in sysprocs table) library. how do i change it to store it point INto another library.
MJ: If you use RUNSQLSTM to compile your SQL source members, run a CHGCURLIB command first to designate the default schema/library into which you want to place the compiled objects. If you use an SQL tool like Run SQL Script in Navigator to compile your SQL objects, run SQL command SET SCHEMA MY_SCHEMA first, where MY_SCHEMA represent the schema into which you want to place your compiled objects. When done compiling, either follow your RUNSQLSTM command(s) with a CHGCURLIB *CRTDFT command, or if using Navigator, run a SET SCHEMA DEFAULT command.

4. when i try to give a wrkobj stored procedure name or the name i saved it with it doesn't show it up. (i am creating a txt file and writing my stored procedure defination there and then runsqlstm.
MJ: if you create an EXTERNAL object, like an EXTERNAL FUNCTION or PROCEDURE, where the executable code is in an external language like RPG, no object gets created. In that case, only a row in SYSFUNCS or SYSPROCS gets created, which stores enough information for the SQL engine to be able to call the EXTERNAL RPG object. Is that what you're doing?[/COLOR]

When compiling SQL objects like functions and procedures, I highly recommend adding SET OPTION DFTRDBCOL = *NONE to the source code, and compiling the object with SQL Naming *SYS in effect. For RUNSQLSTM, there is a NAMING parameter to control that. For IBM i Navigator Run SQL Script, you set the SQL Naming via the Connection JDBC settings. The combination of the two will give you true *LIBL search behavior at runtime, for unqualified table and view names (those not specifying a hard coded schema).

[egami]

Re: Basics on stored procedures

You can alternative put USER in double quotes to make the query work as well.

SELECT RRN,"USER",PGMN FROM session.RETURNEDTABLE;

[satya]

Re: Basics on stored procedures

Hello Amigos,

i am using an sqludf to fetch the last row of a particular table however i am not able to fetch any records.

declare seqnum decimal(6,2);
DECLARE msgtxt CHAR(70);
declare c1 scroll cursor for
with temp as (
select substr(srcdta,26,2) as sbst,substr(srcdta,26,6) as sbst1,
substr(srcdta,26,6) as sbst2 ,substr(srcdta,26,5) as sbst3,a.*
from libl.abs a)
select * from temp where locate('if',sbst,1) <> 0
or locate('begsr',sbst1,1)<> 0 or locate('endsr',sbst2,1)<> 0 or
locate('endif',sbst3,1) <> 0 ;

open c1;
fetch last from c1;
set seqnum =C1.srcseq

I am trying to code fetch last as we do in sqlrpgle to get the last record of a result set.

now i run the above query in interactive strsql and i get the records.

Basically i wanted the last record from the query do i have to go thorugh a loop?

Also how would i access the field from the retunred table.
the srcseq field is not found somehow.
i tried using label of the compound statement too.

[Michael Jones]

Re: Basics on stored procedures

Hi Satya,

Structure your query like this. It will run much faster, because only one row will be selected by the database. Also, if you only need one row, use the INTO clause to capture the columns fetched, since no cursor is needed when only a single row is fetched. Eliminating the cursor improves performance even more.

select    COLUMN_A, COLUMN_B
into      localVarColumnA, localVarColumnB
from      MY_TABLE
order by  THE_COLUMN_WHICH_DEFINES_LAST desc
fetch     first row only

When you combine a DESC sort with a fetch first row only, you get the LAST row. In some cases, your ORDER BY will need multiple columns listed to define the LAST row.

From a performance perspective, when you only want a single row, never use a cursor (always use the INTO clause). When using the INTO clause, always structure your query to guarantee only a single row is returned (in most cases that means incorporate FETCH FIRST ROW ONLY).

Mike