Solved

DB2 V8 SQL STORED PROCEDURE by EXAMPLE

Posted on 2004-10-09
5
1,405 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
[X]
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
  • 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

Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

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 (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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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