MVSFORUMS.com

[shekar123]

Hi,

I am using DB2 version 7 and in my program i trying to use SCROLLABLE CURSOR for selecting but i am unable to precompile as RC = 8.Is it becasue SCROLLABLE CURSOR are not supported for version 7 and it is supported by higher versions.Is there any pre requiste to use SCROLLABLE CUROSRS.I have coded in my program am getting the message

PROGRAM
------------

EXEC SQL
DECLARE C1 SCROLL CURSOR FOR

SELECT DEPTNO,DEPTNAME
FROM DEPT

FOR FETCH ONLY
END-EXEC.


EXEC SQL
OPEN C1
END-EXEC.


EXEC SQL
FETCH ABSOLUTE +3 C1
INTO :WS-DEPTNO, :WS-DEPTNAME
END-EXEC.


EXEC SQL
CLOSE C1
END-EXEC.


MESSAGE
-----------

ILLEGAL SYMBOL "SCROLL". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: STATEMENT
INVALID KEYWORD "CURSOR"; VALID SYMBOLS ARE: <END-OF-STATEMENT>
CURSOR 'C1' WAS NOT DECLARED
ILLEGAL SYMBOL "+". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: INTO USING


Can anyone guide me how to use SCROLLABLE CUROSRS in DB2 ?

[kolusu]

Shekhar123,

Scrollable cursors are indeed supported in DB2 v7 . However you forgot to code INSENSITIVE/sensitive caluse in your declare cursor.

check this link for a detailed explanation of scrollable cursors.

http://www.mvsforums.com/helpboards/viewtopic.php?t=26&highlight=scrollable

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[shekar123]

Thanks Kolusu for your reply and i have gone through the link provided by you for SCROLLABLE CURSORS ,and i tried to declare the cursor like below but still i am not able to precompile the program as i still get RC =8 and i get the message below.I have gone through the link in which it mentions that

DB2 uses declared temporary tables for processing scrollable cursors.
| Therefore, before you can use a scrollable cursor, your database
| administrator needs to create a TEMP database and TEMP table spaces for
| those declared temporary tables. If there is more than one TEMP table
| space in the subsystem, DB2 chooses the table spaces to use for scrollable
| cursors.



My query is my userid has access to the subsystem say DSN ,database name say DB2X and tablespace name say DB2Y then why should the DBA create temporary database and tablespace as already my table is created in the database or DBA should create a database by name TEMP ,a tablespace by name TEMP and give my userid access to it and then i should create a table and use in my program to work out SCROLLABLE CURSORS .Pleae correct if me if i am wrong and what & why is the concept of DECLARED TEMPORARY TABLES needed.Please guide me if i need to get any DBA access to run programs for SCROLLABLE CUROSRS ?

PROGRAM
--------
EXEC SQL
DECLARE C1 INSENSITIVE SCROLL CURSOR FOR

SELECT DEPTNO,DEPTNAME
FROM DEPT

FOR FETCH ONLY
END-EXEC.


MESSAGE
-------
ILLEGAL SYMBOL "INSENSITIVE". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: STATEMENT
ILLEGAL SYMBOL "SCROLL". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <END-OF-STATEMENT
CURSOR 'C1' WAS NOT DECLARED
ILLEGAL SYMBOL "+". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: INTO USING

[kolusu]

[shekar123]

Kolusu,

First of all let me tell you my sequence of operations what i have done :

1.My userid has DB2 allocations to a database called DSN
2.I created a tablespace called TEMP in that database DSN
3.I created a table called TEMP in that tablespace TEMP
4.I am querying from TEMP table only in my program


01 FILLER PIC X(50) VALUE
'SCROLL-CURSOR'.

EXEC SQL
DECLARE C1 INSENSITIVE SCROLL CURSOR FOR

SELECT EMPNO,PHONENO
FROM TEMP
ORDER BY EMPNO
FOR FETCH ONLY
END-EXEC.



EXEC SQL
OPEN C1
END-EXEC.


EXEC SQL
FETCH ABSOLUTE +3 C1
INTO :EMPNO, :PHONENO
END-EXEC.

DISPLAY 'SCROLLABLE CURSOR BEGIN'.
DISPLAY 'EMPNO ' EMPNO.
DISPLAY 'PHONENO ' PHONENO.
DISPLAY 'SCROLLABLE CURSOR END'.

EXEC SQL
CLOSE C1
END-EXEC.


But still i am not able to precompile properly and i am getting the messages as :

DB2 SQL PRECOMPILER MESSAGES
DSNH104I E DSNHSQL LINE 72 COL 28 ILLEGAL SYMBOL "INSENSITIVE". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: STATEMENT
DSNH104I E DSNHPARS LINE 72 COL 40 ILLEGAL SYMBOL "SCROLL". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <END-OF-STATEMENT>
DSNH504I E DSNHSMUD LINE 131 COL 26 CURSOR 'C1' WAS NOT DECLARED
DSNH104I E DSNHPARS LINE 169 COL 36 ILLEGAL SYMBOL "+". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: INTO USING
DSNH521I W DSNHSM3 LINE 200 COL 21 THE CLOSE FOR CURSOR 'C1' IS ASSUMED TO APPLY TO A DYNAMIC ALLOCATE CURSOR STATEMENT
DSNH050I I DSNHMAIN WARNINGS HAVE BEEN SUPPRESSED DUE TO LACK OF TABLE DECLARATIONS
DB2 SQL PRECOMPILER STATISTICS
SOURCE STATISTICS
SOURCE LINES READ: 252
NUMBER OF SYMBOLS: 25
SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 2208
THERE WERE 6 MESSAGES FOR THIS PROGRAM.
THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
246256 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
RETURN CODE IS 8


Moreover when we are using scrollable cursors,my understanding is that the data retrieved is stored in a table called TEMP and from that declared temporary TEMP we are retrieving the values.So in real time situations ,if i want to use this SCROLLABLE concept we cannot use it because we do not deal with declared temporary tables.We create normal DB2 tables for our daily activities and do operations on them.Why do we need declared temporary tables and what are the uses of them.Please guide me how should i solve this purpose .Should i contact DBA for creation of a sample database called TEMP,sample tablespace called TEMP and a table TEMP,but how should the table TEMP be created i mean with what columns ,please help me out ?

[kolusu]

[shekar123]

Code:

Kolusu, I have posted the database name wrongly and i apolize for that.I have done all the operations on Mainframes and here is the code to what i have done : 1.My userid has DB2 allocations to a database called SAMPDB 2.I created a tablespace called TEMP in that database SAMPDB CREATE TABLESPACE TEMP IN SAMPDB;                                                ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                        ---------+---------+---------+---------+---------+---------+---------+---------+ 3.I created a table called TEMP in that tablespace TEMP CREATE TABLE TECH022.TEMP                                                      (EMPNO                  CHAR(6)           NOT NULL,                     FIRSTNAME              VARCHAR(12)       NOT NULL,                     MIDINIT                CHAR(1)           NOT NULL,                     LASTNAME               VARCHAR(15)       NOT NULL,                     WORKDEPT               CHAR(3)                   ,                     PHONENO                CHAR(8)           CONSTRAINT NUMBER CHECK                               (PHONENO >= '00000000' AND                                               PHONENO <= '99999999')   ,                     HIREDATE               DATE                      ,                     JOB                    CHAR(8)                   ,                     EDLEVEL                SMALLINT                  ,                     go-away-spammer-sucker                    CHAR(1)                   ,                     BIRTHDATE              DATE                      ,                     SALARY                 DECIMAL(9,2)              ,        BONUS                  DECIMAL(9,2)              ,        COMM                   DECIMAL(9,2)              ,        PRIMARY KEY            (EMPNO)                   )        IN SAMPDB.TEMP;                                    ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                        ---------+---------+---------+---------+---------+---------+---------+---------+ 4.I am querying from TEMP table only in my program 01 FILLER              PIC X(50) VALUE                'SCROLL-CURSOR'.                                                                                    EXEC SQL                                               DECLARE C1 INSENSITIVE SCROLL CURSOR FOR                                                            SELECT EMPNO,PHONENO                                    FROM TEMP                                        ORDER BY EMPNO                             FOR FETCH ONLY                              END-EXEC.                                    EXEC SQL                 OPEN C1 END-EXEC.        EXEC SQL                               FETCH ABSOLUTE +3 C1          INTO :EMPNO, :PHONENO END-EXEC.                      EXEC SQL                   CLOSE C1 END-EXEC.

I am getting the messages as :

DB2 SQL PRECOMPILER MESSAGES
DSNH104I E DSNHSQL LINE 72 COL 28 ILLEGAL SYMBOL "INSENSITIVE". SOME SYMBOS THAT MIGHT BE LEGAL ARE: STATEMENT
DSNH104I E DSNHPARS LINE 72 COL 40 ILLEGAL SYMBOL "SCROLL". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <END-OF-STATEMENT>
DSNH504I E DSNHSMUD LINE 131 COL 26 CURSOR 'C1' WAS NOT DECLARED
DSNH104I E DSNHPARS LINE 169 COL 36 ILLEGAL SYMBOL "+". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: INTO USING
DSNH521I W DSNHSM3 LINE 200 COL 21 THE CLOSE FOR CURSOR 'C1' IS ASSUMED TO APPLY TO A DYNAMIC ALLOCATE CURSOR STATEMENT
DSNH050I I DSNHMAIN WARNINGS HAVE BEEN SUPPRESSED DUE TO LACK OF TABLE DECLARATIONS


Please guide me further as thow o proceed with my problem.

[shekar123]

Can anybody guide me how do i accomplish the task of using SCROLLABLE cursors for my requirement above ?

[Manas Biswal]

shekar123,
Pls check the following things for me in your DB2 subsystem and confirm back -
1. Is your DB2 subsystem a member of a data sharing group?
2. Is there a TEMP database in your subsystem. By TEMP database, I don't mean a database by the name - 'TEMP' but of type 'TEMP'. It is created as "CREATE DATABASE.......AS TEMP". It can be of any name.
3. Tell me the pagesizes of all the tablespaces in your temp database.
4. Try a "DECLARE C1 INSENSITIVE STATIC SCROLL CURSOR FOR " as the declare statement and let me know if it works.

Get back to me with all the above answers and we will take it from there.

Thanks,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

[shekar123]

Thanks Manas for your reply to my query .Well the answers to your queries :

1.Is your DB2 subsystem a member of a data sharing group?

I do not have any idea that my DB2 subsytem which i use is a member of a data sharing group.How can i know that ? I only have the information that my userid is allocated to a database called SAMPDB under which i work on my DB2 activities.

2.Is there a TEMP database in your subsystem. By TEMP database, I don't mean a database by the name - 'TEMP' but of type 'TEMP'. It is created as "CREATE DATABASE.......AS TEMP". It can be of any name.

Well as of now i created a tablespace called TEMP in SAMPDB database and then i created a table called TEMP and inserted values successfully and i am trying to use that table in my program for which i am unable to precompile successfully.I do not have a database by name TEMP itself in my shop which i was able to find using :


SELECT * FROM SYSIBM.SYSDATABASE ORDER BY NAME


How do i know that the existing databases in my shop have been created / might be created by "CREATE DATABASE.......AS TEMP".

3.Tell me the pagesizes of all the tablespaces in your temp database.

As i do not have a TEMP database ,i do not have any knowledge on the pagesizes.

4.Try a "DECLARE C1 INSENSITIVE STATIC SCROLL CURSOR FOR " as the declare statement and let me know if it works.

I cannot proceed further as my prcompilation is unsuccessful.

Manas,please guide with any further information from you as what should be my approach towards my DBA.

[Manas Biswal]

shekar123,

Run the following query on your DB2 subsystem -

[shekar123]

Manas,

I do not have database listed out in my shop of type T and here is the output of my query.Please let me know how do we proceed further.

Code:

---------+---------+---------+---------+---------+---------+---------+---------+ SELECT * FROM SYSIBM.SYSDATABASE WHERE TYPE = 'T';                              ---------+---------+---------+---------+---------+---------+---------+---------+ NAME      CREATOR   STGROUP   BPOOL       DBID  IBMREQD  CREATEDBY  ROSHARE  TIM ---------+---------+---------+---------+---------+---------+---------+---------+ DSNE610I NUMBER OF ROWS DISPLAYED IS 0                                          DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100                      ---------+---------+---------+---------+---------+---------+---------+---------+

[Manas Biswal]

shekar123,

You need to have a temporary database defined on your subsystem for scrollable cursors to work. Scrollable cursors use declared GTTs which need a temporary database. Talk to your sys DBA to create a temporary database and define tablespaces of pagesize 4k, 8k and 16k(allocate it to the bufferpools of those page sizes) in the database.
The SQL precompiler does not connect to the DB2 catalog and hence this may not be the cause of why your precompilation is failing. But since you need the temp DB anyway, get it created.
About yout precompilation error, did you try the STATIC keyword before SCROLL as I had told you to do.

Thanks,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

[shekar123]

Manas,

Presently the DB2 subsytem in our shop's test environment is down due to maintainance work and i will follow your instructions as said above to contact DBA to create temporary database,tablespaces of sizes and then create table TEMP ,but i do remember that i created cursor using the syntax earlier but i had the same problem of precompilation error.I will let you know updates from my side .

Code:

EXEC SQL DECLARE C1 INSENSITIVE STATIC SCROLL CURSOR FOR SELECT EMPNO,PHONENO FROM TEMP FOR FETCH ONLY END-EXEC.

_________________
Shekar
Grow Technically

[shekar123]

Hai Manas,

My shop's DB2 Test subsytem is up after maintainance work and i tried out using this code but still i am not able to precompile and in my shop there is no database with type 'T'.Please let me know how should be my approach towards DBA, i mean what should he create in sequence of steps to achieve our desired purpose of suing SCROLLABLE cursors successfully as u mentioned :

1.create temporary database TEMP
2.create tablespaces of sizes 4K ,8K AND 16K TEMP
3.create table TEMP

Code:

EXEC SQL                                                 DECLARE C1 INSENSITIVE STATIC SCROLL CURSOR FOR                                                       SELECT EMPNO,PHONENO                                        FROM TEMP                                     FOR FETCH ONLY                                  END-EXEC.          EXEC SQL                 OPEN C1 END-EXEC.        EXEC SQL                               FETCH ABSOLUTE +3 C1          INTO :EMPNO, :PHONENO END-EXEC.                      EXEC SQL                   CLOSE C1 END-EXEC.

Code:

DB2 SQL PRECOMPILER         MESSAGES                                            DSNH104I E     DSNHSQL  LINE 72 COL 28  ILLEGAL SYMBOL "INSENSITIVE". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: STATEMENT DSNH104I E     DSNHPARS LINE 72 COL 40  ILLEGAL SYMBOL "STATIC". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <END-OF-STATEMENT> DSNH504I E     DSNHSMUD LINE 131 COL 26  CURSOR 'C1' WAS NOT DECLARED            DSNH104I E     DSNHPARS LINE 169 COL 36  ILLEGAL SYMBOL "+". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: INTO USING DSNH521I W     DSNHSM3  LINE 200 COL 21  THE CLOSE FOR CURSOR 'C1' IS ASSUMED TO A DYNAMIC ALLOCATE CURSOR STATEMENT DSNH050I I     DSNHMAIN  WARNINGS HAVE BEEN SUPPRESSED DUE TO LACK OF TABLE DECLARATIONS

_________________
Shekar
Grow Technically