IBM Mainframe Forum

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 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 ).

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:

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

Give your query and the output of the query on SYSCOLUMNS.

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 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:

Thanks Akatsukami.. I'll try it out..