Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DB2 V8 SQL STORED PROCEDURE by EXAMPLE

Posted on 2004-10-09
5
1,374 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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