Solved

DB2 V8 SQL STORED PROCEDURE by EXAMPLE

Posted on 2004-10-09
5
1,360 Views
Last Modified: 2008-01-09
DB2 Stored Procedures :
Here I am looking for kind of ""Step-by_Step_SQL PROCEDURE by EXAMPLES""
Can I get one stop solution for the following question(s)
1. BOTH on Unix & WINDOWS 2000: ENVIRONMENT SETUP FOR SQL STORED PROCEDURE
2. Want to Build Pure DATABASE procedures, I dont know about C, C++ Compilers and all othe languages, and its ENVIRONMENTS
3. How do I BUILD, DEBUG, BIND & PACKAE an SQL Procedure
4. fINALLY, I would like to use JDBC to further automate somem stuff . . .

Side Questions:
------------------
1) I am new to stored procedures, have only theoretical knowledge & have a few doubts.
2) What is the type of the system stored procedures i.e. fenced/unfenced. What is difference between these 2 types.
3) Does a DARI process get created for fenced/unfenced for each invocation ultimately flooding the CPU. How do we stop this from happening.
4) When we just say CREATE PROCEDURE SCHEMA.PROCNAME, what type of stored procedure does it become i.e. how do we specify stored procedures to make them fenced/unfenced.
5) Do we need to bind stored procedures & if so how and what all files like .bnd etc.
0
Comment
Question by:aPAPAGARI
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
bondtrader earned 50 total points
ID: 12288989
* First, upgrade to DB2 8.1 fixpak 7 (aka 8.2).  This version does not require ANY C compiler for creating SQL procedures.

* A bind will occur automatically when you run the "create procedure" command - note the LANGUAGE keyword, it denotes SQL

* Here is a sample SQL SP that simply drops an existing SP - it can be built just by changing the terminator character to '@' in the COmmand Center (or Command Editor) and executing it:

CREATE PROCEDURE DROPPROC(IN_PROCNAME varchar(50))

DYNAMIC RESULT SETS 1

LANGUAGE SQL

MODIFIES SQL DATA

BEGIN

DECLARE SQLCODE              INTEGER;

DECLARE l_sqlcode            INTEGER  DEFAULT 0;

DECLARE SQLSTATE             CHAR(5)  DEFAULT '00000';

DECLARE l_sqlstate           CHAR(5)  DEFAULT '00000';

DECLARE l_db2_token_string   VARCHAR(512);

DECLARE l_errmsg             VARCHAR(512);

DECLARE v_Procedure_Exists   INTEGER;

DECLARE v_dynSQL             VARCHAR(256);

DECLARE v_STATEMENT          STATEMENT;



    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING

     err1: BEGIN

          DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING

          err2: BEGIN

             DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING

             err3: BEGIN

                 GET DIAGNOSTICS EXCEPTION 1 l_db2_token_string = DB2_TOKEN_STRING;

             END err3;

             GET DIAGNOSTICS EXCEPTION 1 l_errmsg = MESSAGE_TEXT;

             resignal;

          END err2;

          SELECT  SQLCODE, SQLSTATE INTO l_sqlcode, l_sqlstate from sysibm.sysdummy1;

          resignal;

      END err1;



SET v_Procedure_Exists =

   (Select count(*)

       from SYSIBM.SYSROUTINES

          where ucase(ltrim(rtrim(routinename))) = ucase(IN_PROCNAME));



if v_Procedure_Exists <> 0 then



  SET v_dynSQL = '  DROP PROCEDURE ' || IN_PROCNAME;

  PREPARE v_STATEMENT FROM v_dynSQL;

  EXECUTE v_STATEMENT;

  COMMIT;

END IF;



END@

0
 

Author Comment

by:aPAPAGARI
ID: 12335814
I've a tale (Employee) conatins 60 Columns. Emp_Id is PK (Primary Key) Unique
The number of SUCH Uniques are 4 for this table
Now How do I create/INSERT a 10,000 rows for the above table
I am using the following method to create 1-10 records
--------------------------------------------------------------------
             Insert into Employye (Select Emp_Id,Emp_Name . . . . . . from Employee where Emp_Id = 'EMP0001')
    I can use the above statement as below TO CREATE a new UNIQUE record with 'EMP0002'
             Insert into Employye (Select 'EMP0002',Emp_Name . . . . . . from Employee where Emp_Id = 'EMP0001')

If I repeat the above command (In DB2 Command Center) 10 times then I can create 10 unique records,
NOTE: While Inserting itself, I can replace all UNIQUE_Columns with a UNIQUE Value for above 10 records
----------------------------------------------------------------------------------
HOW DO I PUT  the above command in an ITERATION FOR MILLION times so that I can create a million records?
----------------------------------------------------------------------------------------
OR Is there a better way to create a million records for the above table ?
======================================================================
0
 
LVL 4

Expert Comment

by:bondtrader
ID: 12337727
This looks like a totally different question?
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to conver to Hijri date in SSRS 6 169
iSeries DB2 Query 2 89
finding tables 2 24
Monitor SQL Insert 8 42
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now