Dynamic SQL giving a SQL error of -204

Anu R · New User Joined: 09 Apr 2008 Posts: 18 Location: India

Hi,

I have dynamic SQL in my program.When I try to execute the program my user Id is prefixed to the tablename and I am getting a SQL error of -204.

Eg:
Say I have a table as TABLE1
When I execute a dynamic SQL query the table is interpreted as USERID.TABLE1 and I get the following error

DSNT408I SQLCODE = -204, ERROR: USERID.TABLE1 IS AN UNDEFINED NAME

Please advise

Regards,
Anurekha.R

Andi1982 · Posted: Mon Jun 16, 2008 3:34 pm

Hey,

when you just say table1, it is right that db2 automatically interprets you as creater of the table. If you are not the creator you have to specify the full tablename in the sql: otheruid.table1

Regards
Andi

dbzTHEdinosauer · Posted: Mon Jun 16, 2008 3:36 pm

dynamic sql needs the creator attached to the table name, unlike imbedded sql.

And if you do not have access to the table creator, you need to revisit your design...

Anu R · New User Joined: 09 Apr 2008 Posts: 18 Location: India

Ho can I provide the creator name?the dynamic SQL area in my program is like this

01 SQL-WORK.
05 SQL-INSTRUCTION.
49 SQL-INSTRUCTION-LEN PIC S9(04) COMP.
49 SQL-BODY PIC X(100).
05 SQL-STATIC-BODY-PARTS.
10 SQL-STATIC-1 PIC X(13) VALUE
'SELECT DATE('''.
10 SQL-STATIC-2 PIC X(03) VALUE
''') '.
10 SQL-STATIC-3 PIC X(20) VALUE
' FROM VTRP_DATE_TIME'.
05 WS-VARIABLE-DURATION PIC X(54).
05 WS-VARIABLE-DURATION-LEN PIC 9(02).

Should I include the creator name in SQL-STATIC-3?For doing so I wuld have to increase the lnegth of the variable.I increased the length of the variable,but if I do so I am getting an error during compilation.

It says
1240 IGYPS2121-S "SQL-STATIC-3" was not defined as a data-name

So do i have to change my program or is there any other way I can do this?

Sinkaravelan S · Posted: Mon Jun 16, 2008 5:42 pm

Anu,

Please ask advice from your RACF team..B'cos they only provide the table access to you with creator permission(Data Owners)...

So you need not worry about creator..Just request to your pm to raise a query(Ticket) to RACF team...RACF team is nothing but just TSO id provisioning team..

I think,You don't have a access to that table..

Regards,

ascelepius · New User Joined: 16 Jun 2008 Posts: 35 Location: bangalore

Hi Anu, I dont think not having the creator name of the program is your problem. I donot give creator names in any of our programs that use Dynamic SQL. In fact thats not considered a good practice at some places..

What you could do is, in your bind card, give: