| View previous topic :: View next topic |
| Author |
Message |
user5
Beginner
Joined: 29 Dec 2006
Posts: 9
Topics: 4
|
 Posted: Wed Jan 03, 2007 8:55 am Post subject: CLOB Data used in Linkage variables of a Stored Procedure. |
 |
|
Hi All,
I have to develop a Stored procedure that has to either:
Retreive a record from a table
Insert a record if it is not already existing
Update a record if it is already exisitng
The table definition is as follows:
| Code: |
EXEC SQL DECLARE MB1.T8632_CONFIG_UTIL TABLE
( CONFIG_ID CHAR(3) NOT NULL,
SERV_CTR_CD CHAR(3) NOT NULL,
CONFIG_ID_DS CHAR(30) NOT NULL,
LST_UPDT_DT TIMESTAMP,
LST_UPDT_USER_ID CHAR(8) NOT NULL,
CONFIG_ROWID ROWID NOT NULL,
CONFIG_VAL_TX CLOB(150000) NOT NULL
) END-EXEC.
* COBOL DECLARATION FOR TABLE MB1.T8632_CONFIG_UTIL *
******************************************************************
01 DCLT8632-CONFIG-UTIL.
10 CONFIG-ID PIC X(3).
10 SERV-CTR-CD PIC X(3).
10 CONFIG-ID-DS PIC X(30).
10 LST-UPDT-DT PIC X(26).
10 LST-UPDT-USER-ID PIC X(8).
10 CONFIG-ROWID USAGE SQL TYPE IS ROWID.
10 CONFIG-VAL-TX USAGE SQL TYPE IS CLOB-LOCATOR.
|
Now when a user tries to retreive a record from the table on basis of some key fields:
| Code: |
SELECT CONFIG_VAL_TX
INTO :CONFIG-VAL-TX
FROM T8632_CONFIG_UTIL
WHERE (
T8632_CONFIG_UTIL.CONFIG_ID = :CONFIG-ID AND
T8632_CONFIG_UTIL.SERV_CTR_CD = :SERV-CTR-CD
)
|
The SP has to return this large object data in it's output variable.
I have defined the output variable in the linkage section as :
| Code: |
01 WL-OU-CONFIG-VAL-TX USAGE IS SQL
TYPE IS CLOB(150000). |
Q 1)
Is this definition correct?
But the above Select Query retreived the data in the dclgen var of the table. So I move the data in the dclgen to the linkage var.
MOVE CONFIG-VAL-TX TO WL-OU-CONFIG-VAL-TX
But the definition of the dclgen var is :
10 CONFIG-VAL-TX USAGE SQL TYPE IS CLOB-LOCATOR.
Q 2)
Thus is the above move valid?
Also, when the SP has to insert a record in this table, the front end passes a Large object data. The definition of the Input variable in the LInkage section is :
01 WL-IN-CONFIG-VAL-TX USAGE IS SQL
TYPE IS CLOB(150000).
Q 3)
Is the input var is correctly defined?
Q 4)
Using the definitions mentioned above, when I try running the SP using the Stored Procedure Builer , I get SQLSTATE=38503 for the first SQL that the SP encounters.
In case of retreival the SP Builder application closes down.
Please suggest
Thanks & Regards.
User5 |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Jan 03, 2007 9:49 am Post subject: |
|
|
User5,
Answers for questions 1 thru 3 can be found here . Read this section for a detailed explanation of Programming for large objects (LOBs)
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/3.2?DT=20010710165542
| Quote: |
4)
Using the definitions mentioned above, when I try running the SP using the Stored Procedure Builer , I get SQLSTATE=38503 for the first SQL that the SP encounters.
|
Sqlstate=38503 is an error when a user-defined function or procedure has abnormally terminated (abend). You say that you got this error on your first sql statement. Make sure that your SP is defined correctly.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
user5
Beginner
Joined: 29 Dec 2006
Posts: 9
Topics: 4
|
| Posted: Thu Jan 04, 2007 6:04 am Post subject: Reg: CLOB Data used in Linkage variables of Stored Procedure |
|
|
Hello Kolusu,
While I was reading some articles on the link that you had sent, I came across : LOB materialisation.
In this article it is mentioned that:
DB2 must materialize LOBs when your application program:
* Moves a LOB into or out of a stored procedure
In my case I have to either insert a LOB or update it or retreive it from the table.
So that means I have to adopt LOB materialisation.
What I understand of LOB materialisation.
Selecting and upadating & inserting the actual LOB data instead of the LOB Locators.
Is my understanding correct? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 04, 2007 9:46 am Post subject: |
|
|
user5,
Well it depends on the size of your LOB.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
user5
Beginner
Joined: 29 Dec 2006
Posts: 9
Topics: 4
|
| Posted: Fri Jan 05, 2007 1:42 am Post subject: Inserting or selecting CLOB data from tables |
|
|
But since I am trying to insert update or select a CLOB column of the table,
is it that I cannot use locators?
Is it that I necessarily have to exchange the actual CLOB data?
Regards,
User5 |
|
| Back to top |
|
|
Vizz0
Beginner
Joined: 20 Oct 2006
Posts: 4
Topics: 2
|
| Posted: Thu Jan 25, 2007 12:43 am Post subject: |
|
|
We are getting this error
"SQL0430N SQLSTATE=38503"
But we are not using CLOB? Any suggestion?
_________________
Regards,
Vizz0 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Jan 25, 2007 8:25 am Post subject: |
|
|
Vizz0,
Did you read the 2nd post in this topic?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
