Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 - CALL to a STORED PROC

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
smdayaz.tpt

New User


Joined: 04 Oct 2007
Posts: 5
Location: Bangalore

PostPosted: Tue Jul 22, 2008 4:45 pm    Post subject: DB2 - CALL to a STORED PROC
Reply with quote

Hi All,


[DROP PROCEDURE RuleCheckForAccessRights (CHAR(30),
VARCHAR(30),
VARCHAR(30),
VARCHAR(30),
VARCHAR(30),
INTEGER,
VARCHAR(25) )!

CREATE PROCEDURE RuleCheckForAccessRights (v_UserName CHAR(30),
v_legl_ent_name VARCHAR(30),
v_legl_ent_prd_grp VARCHAR(30),
v_legl_ent_region VARCHAR(30),
v_Type VARCHAR(30),
OUT v_ErrorType INTEGER,
OUT v_ErrorCode VARCHAR(25) )
LANGUAGE SQL

BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE l_error CHAR(5) DEFAULT '00000';

DECLARE v_UserId INTEGER;

DECLARE v_GroupId INTEGER;

DECLARE loc RESULT_SET_LOCATOR VARYING;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET l_error = '00000';

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING

BEGIN

SET l_error = SQLSTATE;

IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN

RESIGNAL;

END IF;

END;

DECLARE GLOBAL TEMPORARY TABLE tempAssignedGroups (groupID INTEGER NOT NULL,
groupName VARCHAR(50) NOT NULL,
description VARCHAR(140) NOT NULL);

DECLARE GLOBAL TEMPORARY TABLE tempUserGroups (groupID INTEGER NOT NULL);

DECLARE GLOBAL TEMPORARY TABLE tempResultGroups (groupID INTEGER NOT NULL);

SELECT UserId
INTO v_UserId
FROM up
WHERE UserName = v_UserName
FETCH FIRST 1 ROWS ONLY;

BEGIN
INSERT INTO SESSION.tempUserGroups
(SELECT groupid FROM Mapping
WHERE UserId = v_UserId);


COMMIT;


INSERT INTO SESSION.tempAssignedGroups(
CALL ReadAssignedGroups (v_legl_ent_name, v_legl_ent_prd_grp, v_legl_ent_region, v_Type);
);

COMMIT;

IF ((SELECT COUNT(*) FROM SESSION.tempAssignedGroups) > 0) THEN

INSERT INTO SESSION.tempResultGroups
(SELECT groupid FROM SESSION.tempUserGroups A WHERE EXISTS
(SELECT 1 FROM SESSION.tempAssignedGroups B where A.GroupID = B.GroupID));

COMMIT;

IF ((SELECT COUNT(*) FROM SESSION.tempResultGroups) > 0) THEN

SET v_ErrorType = 0;

SET v_ErrorCode = '';

ELSE

SET v_ErrorType = 2;

SET v_ErrorCode = '50128';

END IF;

ELSE

SET v_ErrorType = 0;

SET v_ErrorCode = '';

END IF;
END;
END!][/
code]



I tried to deploy the above PROCEDURE to the DB2 database but it erred out while deploying.I'm using the IBM DB2 Toolkit to deploy the PROC.
I'm getting the following error directed by the toolkit

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0104N An unexpected token "CALL ReadAssignedGroups (v_legl" was found

following "tempAssignedGroups( ". Expected tokens may include:

"<query_expr_body>". LINE NUMBER=65. SQLSTATE=42601


Can anyone clarify how "CALL to a stored PROC" should be handled within DB2 and also suggest me how to insert the result set of the PROC into a table...

Thanks in Advance,
Ayaz
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 22, 2008 5:14 pm    Post subject:
Reply with quote

You cannot call ReadAssignedGroups within the INSERT statement ...
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 22, 2008 5:23 pm    Post subject:
Reply with quote

Is ReadAssignedGroups an UDF ?
Back to top
View user's profile Send private message
smdayaz.tpt

New User


Joined: 04 Oct 2007
Posts: 5
Location: Bangalore

PostPosted: Tue Jul 22, 2008 6:18 pm    Post subject: Reply to: DB2 - CALL to a STORED PROC
Reply with quote

Hi ashimer,

ReadAssignedGroups is not a UDF its also an Stored PROC its contains below mentioned code

[Drop procedure ReadAssignedGroups
@
CREATE PROCEDURE ReadAssignedGroups (v_legl_ent_name VARCHAR(30),
v_legl_ent_prd_grp VARCHAR(30),
v_legl_ent_region VARCHAR(30),
v_Type VARCHAR(30) )
LANGUAGE SQL
P1:BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_error CHAR(5) DEFAULT '00000';
DECLARE CONTINUE HANDLER FOR NOT FOUND

SET l_error = '00000';

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING

BEGIN

SET l_error = SQLSTATE;

IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN

RESIGNAL;

END IF;

END;


DECLARE GLOBAL TEMPORARY TABLE tempIncluded (groupID INTEGER NOT NULL,
groupName VARCHAR(50) NOT NULL,
description VARCHAR(140) NOT NULL);
DECLARE GLOBAL TEMPORARY TABLE tempExcluded (groupID INTEGER NOT NULL,
groupName VARCHAR(50) NOT NULL,
description VARCHAR(140) NOT NULL);
DECLARE GLOBAL TEMPORARY TABLE includedGroups (groupID INTEGER NOT NULL,
groupName VARCHAR(50) NOT NULL,
description VARCHAR(140) NOT NULL);




IF (v_Type = 'LegalEntity') THEN
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname is null
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
ELSE
IF (v_Type = 'LOB') THEN
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname is null
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
--| --Checking whether this lob is excluded from any ratabase.Groups or not
INSERT INTO SESSION.tempExcluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName is null
AND a.IsExcluded ='Y'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.includedGroups
(SELECT * FROM SESSION.tempIncluded WHERE GroupID not in (SELECT GroupID FROM SESSION.tempExcluded));
COMMIT;
ELSE
IF (v_Type = 'REGION') THEN
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname is null
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName is null
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.includedGroups
(SELECT * FROM SESSION.tempIncluded WHERE GroupID not in (SELECT GroupID FROM SESSION.tempExcluded));
COMMIT;
DROP TABLE SESSION.tempExcluded;
COMMIT;
DROP TABLE SESSION.tempIncluded;
COMMIT;
INSERT INTO SESSION.tempIncluded
(SELECT * FROM Session.includedGroups);
COMMIT;
INSERT INTO SESSION.tempIncluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName = v_legl_ent_region
AND a.IsExcluded ='N'
AND a.GroupID = b.GroupID);
COMMIT;
INSERT INTO SESSION.tempExcluded
(SELECT b.groupID,b.groupName,b.description
FROM ratabase.objectmapping a ,ratabase.Groups b
WHERE a.LEName = v_legl_ent_name
AND a.LOBname = v_legl_ent_prd_grp
AND a.RegionName = v_legl_ent_region
AND a.IsExcluded ='Y'
AND a.GroupID = b.GroupID);
COMMIT;
END IF;
END IF;
END IF;
P2:BEGIN
--| --output
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT DISTINCT GROUPID,
groupName,
description
FROM SESSION.tempIncluded;
--| --Output
DECLARE temp_cursor1 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT DISTINCT GROUPID,
groupName,
description
FROM SESSION.tempIncluded;
--| --Out put
DECLARE temp_cursor2 CURSOR WITH HOLD WITH RETURN TO CLIENT
FOR SELECT DISTINCT GROUPID,
groupName,
description
FROM SESSION.tempIncluded
WHERE GroupID NOT IN (SELECT GroupID
FROM SESSION.tempExcluded);
IF (v_Type = 'LegalEntity') THEN
--| --output
OPEN temp_cursor;
ELSE
IF (v_Type = 'LOB') THEN
OPEN temp_cursor1;
ELSE
IF (v_Type = 'REGION') THEN
--| --output
OPEN temp_cursor2;
END IF;
END IF;
END IF;

END P2;
END P1
@]


As far as concern ReadAssignedGroups PROC independently works fine.

Please have look on the code and let me know your valuable comments.


Thanks in Advance,
Ayaz

[/code]
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 22, 2008 7:10 pm    Post subject:
Reply with quote

OK ... Here you cannot use the following

Code:


INSERT INTO SESSION.tempAssignedGroups(
CALL ReadAssignedGroups (v_legl_ent_name, v_legl_ent_prd_grp, v_legl_ent_region, v_Type);
);



You cannot use a CALL inside and INSERT statement ... Now your ReadAssignedGroups PROC will open one cursor and return the control back to RuleCheckForAccessRights .. now what you need to do is declare an RESULT_SET_LOCATOR to associate the open cursor in your calling PROC ...

You may do the following

Code:


BEGIN DECLARE SECTION;
RESULT_SET_LOCATOR VARYING locater1;
END DECLARE SECTION;

CALL ReadAssignedGroups (:v_legl_ent_name, :v_legl_ent_prd_grp, :v_legl_ent_region, :v_Type);

ASSOCIATE LOCATOR (:locater1) WITH PROCEDURE ReadAssignedGroups ;

ALLOCATE CSR1 CURSOR FOR RESULT SET :locater1;

FETCH CSR1 INTO :host variables...;


The cursor name depends on the v_Type ..

after each fetch insert into your temporary table until sqlcode = 100 ...

I hope this helps ... this is just an algorithm ... work on it ...
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 22, 2008 7:23 pm    Post subject:
Reply with quote

As visible from your code you are issuing lots of commits .... please declare your temporary tables as ON COMMIT PRESERVE ROWS .. or else after commit there wont be anything left in your temporary tables for the PROC to work on .... Be careful with commits ...
Back to top
View user's profile Send private message
smdayaz.tpt

New User


Joined: 04 Oct 2007
Posts: 5
Location: Bangalore

PostPosted: Wed Jul 23, 2008 2:42 pm    Post subject: Reply to: DB2 - CALL to a STORED PROC
Reply with quote

Hi Ashimer,


I tried your algorithm it works fine and i'm able to deploy it to the DB.Thanks a ton for your timely help...


Thanks & Regards,
Ayaz
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jul 23, 2008 2:44 pm    Post subject:
Reply with quote

You are welcome icon_biggrin.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts REXX to view the PGM or PROC jackzhang75 CLIST & REXX 11 Tue Jun 20, 2017 1:20 am
No new posts Need help in REXX CALL program Raje1002 CLIST & REXX 5 Wed Apr 19, 2017 11:18 pm
No new posts Need help in triggering a PL1 module ... Varungv99 IMS DB/DC 0 Fri Apr 07, 2017 10:01 am
No new posts Jcl pram vs proc parms ashek15 JCL & VSAM 3 Thu Mar 30, 2017 6:50 pm
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us