IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 - CALL to a STORED PROC


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error while running web tool kit REXX... CLIST & REXX 5
No new posts Call program, directly from panel CLIST & REXX 9
No new posts Batch call online program, EXCI task ... CICS 3
No new posts CSQBGET - Call giving completion code... COBOL Programming 3
No new posts Invoke stored procedure via batch JCL. DB2 2
Search our Forums:

Back to Top