Solved

DB2 V8 SQL STORED PROCEDURE by EXAMPLE

Posted on 2004-10-09
5
1,353 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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

15 Experts available now in Live!

Get 1:1 Help Now