DB2 Cobol Problem



IBM's flagship relational database management system

DB2 Cobol Problem

Postby Aditya Jain » Fri Dec 30, 2011 9:35 pm

I've just got DB2 access at work and this is the first program I am trying to run. I have got a theoretical idea about dbrm, packages and plans. I have very little knowledge on endevor.
I know a table that is access by a table that is access by a program from endevor. I am trying to perform a select query on that table.
Let’s consider the owner name of the table is XXXX and table name is YYYY
I’ve made two JCLs one compile and the second run
The first one has the DB2 precompiler DSNHPC, Cobol Compiler IGYCRCTL, Linker IEWL ( alias for IEWBLINK) and IKJEFT01 for binding dbrm with package.
The second runs the program and takes the package name via IKJEFT01
Problem 1:
I am unable to perform select queries that have the where clause
It says the column is not found in the table
I’ve tried putting the exact column name mentioned in the declare table clause and the working storage section entry that represents the table. Both don’t work.
Problem 2:
I’ve not made my own package but packaged using a random package that I came across in the DB2 Admin’s System Catalog option. The package I am using has the same owner XXXX of the table XXXX.YYYY
The code successfully binds but says that “doesn’t belong to the context where used”
Problem 3:
As I somehow managed to compile the above db2 program, I tried to ran it with the JCL but I didn’t have a plan name yet. So I tried adding a plan with the DB2I option but it returned that my ID doesn’t have authorization to perform select on that table whereas I can go to the DB2A option and perform SQL statements directly. I tried putting in a random plan name but it returned MAXCC0 and SQLcode 81Q
Problem 4:
Is there an option or any way by which I can get a list on tables specifying what accesses I have on them ?
Problem 5:
This is more of a question rather than a problem.
When I see the program that has been moved to endevor, it has the declare table followed by only the table name i.e. declare table YYYY but when I used DCLGEN to make the declarations for that table it added the owner name to it too i.e. Declare table XXXX.YYYY

Please help.
Aditya Jain
 
Posts: 11
Joined: Fri Dec 30, 2011 8:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Cobol Problem

 

Re: DB2 Cobol Problem

Postby Akatsukami » Fri Dec 30, 2011 10:52 pm

Aditya Jain wrote:I’ve made two JCLs one compile and the second run

Note that you should have used the standard compile/link/bind etc.JCL provided by your shop; running your own JCL is ground for dismissal in some shops.

Assuming that you're still employed, however...
The second runs the program and takes the package name via IKJEFT01

Note the package name is not wanted, the plan name is (unless you're running some sort of process that does a plan bind just before the program is run each time :shock: ).
Problem 1:
I am unable to perform select queries that have the where clause
It says the column is not found in the table
I’ve tried putting the exact column name mentioned in the declare table clause and the working storage section entry that represents the table. Both don’t work.

It sounds as if you're a bit confused. Let us go back a couple of steps.

Presumably your program has embedded in it a SQL statement that looks something like:
SELECT THIS_COL, THAT_COL FROM YYYY
WHERE FOO = 'BAR'

with suitable delimiters for COBOL (not having written any COBOL in years, I forget what they are). You are getting a negative SQLCODE (probably -204) at package bind time.

Now, you must realize that DECLARE TABLE is of little value; lose it. Using QMF, SPUFI, DSNTEP*, or whatever tool is convenient and comfortable for you, run the query
SELECT COLNO, SUBSTR(NAME,1,18) AS NAME, COLTYPE, LENGTH, NULLS
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME='YYYY'

Give your query and the output of the query on SYSCOLUMNS.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: DB2 Cobol Problem

Postby Aditya Jain » Wed Jan 18, 2012 10:17 pm

Thanks Akatsukami your input was very valuable
I’ve solved almost all the problems
Answer to Problem 1
As Akatsukami pointed out, I was missing cobol delimiters
I have declared a group level 01 item
So to reference sub items I had to use Lvl01Item.SubItem format in the query also a ‘:’ was required before working storage variable references.
Answer to Problem 2
Again as Akatsukami pointed out, the plan name matters the package ain’t of much importance.. I chose the random package by seeing its owner was same as the table’s
Answer to Problem 3
Again as Akatsukami pointed out, the right plan name is needed. There is no alternative to that.
Answer to Problem 4
No clue on that one.
Yes it has to be declared Owner.TableName
Aditya Jain
 
Posts: 11
Joined: Fri Dec 30, 2011 8:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Cobol Problem

Postby Akatsukami » Thu Jan 19, 2012 12:20 am

Aditya Jain wrote:Problem 4:
Is there an option or any way by which I can get a list on tables specifying what accesses I have on them ?

This query should give you all granted privileges on tables and views:
SELECT SUBSTR(TCREATOR,1,8) AS OWNER, TTNAME AS TABLE FROM SYSIBM.SYSTABAUTH
WHERE GRANTEE = 'your-id'
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: DB2 Cobol Problem

Postby Aditya Jain » Sat Mar 24, 2012 11:18 am

Thanks Akatsukami.. I'll try it out..
Aditya Jain
 
Posts: 11
Joined: Fri Dec 30, 2011 8:55 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post