Creator-ids & dynamic SQLs

Navin Agrawal

Creator-ids & dynamic SQLs
Dear listers,

This might look like a quick question for most of you out there.

My question is related to dynamic SQL. I'm looking for ways to run a dynamic SQL which doesn't have creator-id prefixed to the table name. So far, I'm bugged by SQLCODE -104 which looks hard to get past.

Here is the SQL I'm trying to execute dynamically:

SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM TRD_PLM_BR_PGRP_V.
- Prepare fails with SQLCODE -104

SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM CREATOR.TRD_PLM_BR_PGRP_V.
- Executes successfully.

Thanks in advance.
Navin Agrawal


---------------------------------
Do you Yahoo!?
Free Pop-Up Blocker - Get it now

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Pantazis Pantazi

Re: Creator-ids & dynamic SQLs
(in response to Navin Agrawal)

DB2 during dynamic SQL attaches the username of whomever issues the SQL as
the creator of table
and that is probably the reason why you get the abend.

An alternative would be to create a synonym for your table and use that
instead.

Regards,

Pantazis Pantazi
Database Administrator

Enterprise Systems Support
Information Services Division
Laiki Group, Cyprus
Tel: +357-22812530, Fax: +357-22812583
Email: [login to unmask email]


|---------+---------------------------------->
| | Navin Agrawal |
| | <[login to unmask email]>|
| | Sent by: DB2 Data Base |
| | Discussion List |
| | <[login to unmask email]> |
| | 04/12/2003 01:26 ìì |
| | Please respond to DB2 |
| | Database Discussion |
| | list at IDUG |
| | |
| | |
|---------+---------------------------------->
>---------------------------------------------------------------------------------------------------------|
| |
| To: [login to unmask email] |
| cc: |
| bcc: |
| Subject: Creator-ids & dynamic SQLs |
| |
>---------------------------------------------------------------------------------------------------------|




Dear listers,

This might look like a quick question for most of you out there.

My question is related to dynamic SQL. I'm looking for ways to run a
dynamic SQL which doesn't have creator-id prefixed to the table name. So
far, I'm bugged by SQLCODE -104 which looks hard to get past.

Here is the SQL I'm trying to execute dynamically:
SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM TRD_PLM_BR_PGRP_V.
- Prepare fails with SQLCODE -104

SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM
CREATOR.TRD_PLM_BR_PGRP_V.
- Executes successfully.

Thanks in advance.
Navin Agrawal


Do you Yahoo!?
Free Pop-Up Blocker - Get it now
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm






********************************************************************************************
Privileged/Confidential information may be contained in this message and
may be subject to legal privilege. Access to this e-mail by anyone other
than the intended recipient is unauthorised. If you are not the intended
recipient (or responsible for delivery of the message to such person), you
may not use, copy, distribute or deliver to anyone this message (or any
part of its contents) or take any action in reliance on it. In such case,
you should destroy this message, and notify us immediately.

If you have received this email in error, please notify us immediately by
e-mail or telephone and delete the e-mail from any computer. If you or your
employer does not consent to internet e-mail messages of this kind, please
notify us immediately.

All reasonable precautions have been taken to ensure no viruses are present
in this e-mail. As we cannot accept responsibility for any loss or damage
arising from the use of this e-mail or attachments we recommend that you
subject these to your virus checking procedures prior to use.

The views, opinions, conclusions and other information expressed in this
electronic mail are not given or endorsed by Laiki Group unless otherwise
indicated by an authorised representative independent of this message.
********************************************************************************************

Vin db

Re: Creator-ids & dynamic SQLs
(in response to Pantazis Pantazi)
I thought about that also, but again
if the dynamic SQL, which is getting prepared at runtime, derives the other bind parameters from the plan/package under which is getting bothered, why should it not get the CREATORID too from the same plan/package ?

any thoughts ?

Regards
Navin Agrawal


[login to unmask email] wrote:

DB2 during dynamic SQL attaches the username of whomever issues the SQL as
the creator of table
and that is probably the reason why you get the abend.

An alternative would be to create a synonym for your table and use that
instead.

Regards,

Pantazis Pantazi
Database Administrator

Enterprise Systems Support
Information Services Division
Laiki Group, Cyprus
Tel: +357-22812530, Fax: +357-22812583
Email: [login to unmask email]


|---------+---------------------------------->
| | Navin Agrawal |
| | |
| | Sent by: DB2 Data Base |
| | Discussion List |
| | |
| | 04/12/2003 01:26 ìì |
| | Please respond to DB2 |
| | Database Discussion |
| | list at IDUG |
| | |
| | |
|---------+---------------------------------->
>---------------------------------------------------------------------------------------------------------|
| |
| To: [login to unmask email] |
| cc: |
| bcc: |
| Subject: Creator-ids & dynamic SQLs |
| |
>---------------------------------------------------------------------------------------------------------|




Dear listers,

This might look like a quick question for most of you out there.

My question is related to dynamic SQL. I'm looking for ways to run a
dynamic SQL which doesn't have creator-id prefixed to the table name. So
far, I'm bugged by SQLCODE -104 which looks hard to get past.

Here is the SQL I'm trying to execute dynamically:
SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM TRD_PLM_BR_PGRP_V.
- Prepare fails with SQLCODE -104

SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM
CREATOR.TRD_PLM_BR_PGRP_V.
- Executes successfully.

Thanks in advance.
Navin Agrawal


Do you Yahoo!?
Free Pop-Up Blocker - Get it now
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm






********************************************************************************************
Privileged/Confidential information may be contained in this message and
may be subject to legal privilege. Access to this e-mail by anyone other
than the intended recipient is unauthorised. If you are not the intended
recipient (or responsible for delivery of the message to such person), you
may not use, copy, distribute or deliver to anyone this message (or any
part of its contents) or take any action in reliance on it. In such case,
you should destroy this message, and notify us immediately.

If you have received this email in error, please notify us immediately by
e-mail or telephone and delete the e-mail from any computer. If you or your
employer does not consent to internet e-mail messages of this kind, please
notify us immediately.

All reasonable precautions have been taken to ensure no viruses are present
in this e-mail. As we cannot accept responsibility for any loss or damage
arising from the use of this e-mail or attachments we recommend that you
subject these to your virus checking procedures prior to use.

The views, opinions, conclusions and other information expressed in this
electronic mail are not given or endorsed by Laiki Group unless otherwise
indicated by an authorised representative independent of this message.
********************************************************************************************


---------------------------------
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Pantazis Pantazi

Re: Creator-ids & dynamic SQLs
(in response to Vin db)

As long as the owner and the qualifier of the package is the correct one,
it will work just fine

Regards,

Pantazis Pantazi
Database Administrator

Enterprise Systems Support
Information Services Division
Laiki Group, Cyprus
Tel: +357-22812530, Fax: +357-22812583
Email: [login to unmask email]


|---------+---------------------------------->
| | Vin db |
| | <[login to unmask email]> |
| | Sent by: DB2 Data Base |
| | Discussion List |
| | <[login to unmask email]> |
| | 04/12/2003 01:50 ¦Ì¦Ì |
| | Please respond to DB2 |
| | Database Discussion |
| | list at IDUG |
| | |
| | |
|---------+---------------------------------->
>---------------------------------------------------------------------------------------------------------|
| |
| To: [login to unmask email] |
| cc: |
| bcc: |
| Subject: Re: Creator-ids & dynamic SQLs |
| |
>---------------------------------------------------------------------------------------------------------|




I thought about that also, but again
if the dynamic SQL, which is getting prepared at runtime, derives the other
bind parameters from the plan/package under which is getting bothered, why
should it not get the CREATORID too from the same plan/package ?

any thoughts ?

Regards
Navin Agrawal


[login to unmask email] wrote:

DB2 during dynamic SQL attaches the username of whomever issues the SQL as
the creator of table
and that is probably the reason why you get the abend.

An alternative would be to create a synonym for your table and use that
instead.

Regards,

Pantazis Pantazi
Database Administrator

Enterprise Systems Support
Information Services Division
Laiki Group, Cyprus
Tel: +357-22812530, Fax: +357-22812583
Email: [login to unmask email]


|---------+---------------------------------->
| | Navin Agrawal |
| | |
| | Sent by: DB2 Data Base |
| | Discussion List |
| | |
| | 04/12/2003 01:26 ¨¬¨¬ |
| | Please respond to DB2 |
| | Database Discussion |
| | list at IDUG |
| | |
| | |
|---------+---------------------------------->
>---------------------------------------------------------------------------------------------------------|

| |
| To: [login to unmask email] |
| cc: |
| bcc: |
| Subject: Creator-ids & dynamic SQLs |
| |
>---------------------------------------------------------------------------------------------------------|





Dear listers,

This might look like a quick question for most of you out there.

My question is related to dynamic SQL. I'm looking for ways to run a
dynamic SQL which doesn't have creator-id prefixed to the table name. So
far, I'm bugged by SQLCODE -104 which looks hard to get past.

Here is the SQL I'm trying to execute dynamically:
SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM TRD_PLM_BR_PGRP_V.
- Prepare fails with SQLCODE -104

SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM
CREATOR.TRD_PLM_BR_PGRP_V.
- Executes successfully.

Thanks in advance.
Navin Agrawal


Do you Yahoo!?
Free Pop-Up Blocker - Get it now
---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send
the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm






********************************************************************************************

Privileged/Confidential information may be contained in this message and
may be subject to legal privilege. Access to this e-mail by anyone other
than the intended recipient is unauthorised. If you are not the intended
recipient (or responsible for delivery of the message to such person), you
may not use, copy, distribute or deliver to anyone this message (or any
part of its contents) or take any action in reliance on it. In such case,
you should destroy this message, and notify us immediately.

If you have received this email in error, please notify us immediately by
e-mail or telephone and delete the e-mail from any computer. If you or
your
employer does not consent to internet e-mail messages of this kind, please
notify us immediately.

All reasonable precautions have been taken to ensure no viruses are
present
in this e-mail. As we cannot accept responsibility for any loss or damage
arising from the use of this e-mail or attachments we recommend that you
subject these to your virus checking procedures prior to use.

The views, opinions, conclusions and other information expressed in this
electronic mail are not given or endorsed by Laiki Group unless otherwise
indicated by an authorised representative independent of this message.
********************************************************************************************


Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm






********************************************************************************************
Privileged/Confidential information may be contained in this message and
may be subject to legal privilege. Access to this e-mail by anyone other
than the intended recipient is unauthorised. If you are not the intended
recipient (or responsible for delivery of the message to such person), you
may not use, copy, distribute or deliver to anyone this message (or any
part of its contents) or take any action in reliance on it. In such case,
you should destroy this message, and notify us immediately.

If you have received this email in error, please notify us immediately by
e-mail or telephone and delete the e-mail from any computer. If you or your
employer does not consent to internet e-mail messages of this kind, please
notify us immediately.

All reasonable precautions have been taken to ensure no viruses are present
in this e-mail. As we cannot accept responsibility for any loss or damage
arising from the use of this e-mail or attachments we recommend that you
subject these to your virus checking procedures prior to use.

The views, opinions, conclusions and other information expressed in this
electronic mail are not given or endorsed by Laiki Group unless otherwise
indicated by an authorised representative independent of this message.
********************************************************************************************

teldb2kals

Re: Creator-ids & dynamic SQLs
(in response to Pantazis Pantazi)
Hi Navin,

What plan/package are you running the sql under ? Have you tried to bind
the plan with DYNAMICRULES(BIND) ? The default is RUN, which takes the
sqlid of the user running the sql for unqualified objects.
(More info on dynamicrules behaviour is in the command reference).

Regards,
Kals.

On Thu, 4 Dec 2003 03:50:56 -0800, Vin db <[login to unmask email]> wrote:

>I thought about that also, but again
>if the dynamic SQL, which is getting prepared at runtime, derives the
other bind parameters from the plan/package under which is getting
bothered, why should it not get the CREATORID too from the same
plan/package ?
>
>any thoughts ?
>
>Regards
>Navin Agrawal
>
>
>[login to unmask email] wrote:
>
>DB2 during dynamic SQL attaches the username of whomever issues the SQL as
>the creator of table
>and that is probably the reason why you get the abend.
>
>An alternative would be to create a synonym for your table and use that
>instead.
>
>Regards,
>
>Pantazis Pantazi
>Database Administrator
>
>Enterprise Systems Support
>Information Services Division
>Laiki Group, Cyprus
>Tel: +357-22812530, Fax: +357-22812583
>Email: [login to unmask email]
>
>
>|---------+---------------------------------->
>| | Navin Agrawal |
>| | |
>| | Sent by: DB2 Data Base |
>| | Discussion List |
>| | |
>| | 04/12/2003 01:26 ìì |
>| | Please respond to DB2 |
>| | Database Discussion |
>| | list at IDUG |
>| | |
>| | |
>|---------+---------------------------------->
>>--------------------------------------------------------------------------
-------------------------------|
>| |
>| To: [login to unmask email] |
>| cc: |
>| bcc: |
>| Subject: Creator-ids & dynamic SQLs |
>| |
>>--------------------------------------------------------------------------
-------------------------------|
>
>
>
>
>Dear listers,
>
>This might look like a quick question for most of you out there.
>
>My question is related to dynamic SQL. I'm looking for ways to run a
>dynamic SQL which doesn't have creator-id prefixed to the table name. So
>far, I'm bugged by SQLCODE -104 which looks hard to get past.
>
>Here is the SQL I'm trying to execute dynamically:
>SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM TRD_PLM_BR_PGRP_V.
>- Prepare fails with SQLCODE -104
>
>SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM
>CREATOR.TRD_PLM_BR_PGRP_V.
>- Executes successfully.
>
>Thanks in advance.
>Navin Agrawal
>
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: Creator-ids & dynamic SQLs
(in response to teldb2kals)
-104 is a syntax error. So I only can imagine that the problem is your
terminating character (full stop?) after the SQL string. With dynamic
prepare, no terminating char is allowed, even no semicolon.

If you look at sqlerrd(5), you get the position of the error in your
SQL string (starting by 1). Maybe this can help you to fix the error.
This applies only to errors of "syntax" kind (-104, for example).

Regards

Bernd



Am Don, 04 Dez 2003 schrieben Sie:
>
> Dear listers,
>
> This might look like a quick question for most of you out there.
>
> My question is related to dynamic SQL. I'm looking for ways to run a dynamic SQL which doesn't have creator-id prefixed to the table name. So far, I'm bugged by SQLCODE -104 which looks hard to get past.
>
> Here is the SQL I'm trying to execute dynamically:
>
> SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM TRD_PLM_BR_PGRP_V.
> - Prepare fails with SQLCODE -104
>
> SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM CREATOR.TRD_PLM_BR_PGRP_V.
> - Executes successfully.
>
> Thanks in advance.
> Navin Agrawal
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Vin db

Re: Creator-ids & dynamic SQLs
(in response to Bernd Oppolzer)
Hi Bernd,
Apologies for that typo.. When I pasted the SQL from my sysout and addes some comment, those 'fullstops' got added into.

The details of -104 says something about the 'token', which probably points to the missing creator-id(my guess).

Regards
Navin Agrawal
Bernd Oppolzer <[login to unmask email]> wrote:
-104 is a syntax error. So I only can imagine that the problem is your
terminating character (full stop?) after the SQL string. With dynamic
prepare, no terminating char is allowed, even no semicolon.

If you look at sqlerrd(5), you get the position of the error in your
SQL string (starting by 1). Maybe this can help you to fix the error.
This applies only to errors of "syntax" kind (-104, for example).

Regards

Bernd



Am Don, 04 Dez 2003 schrieben Sie:
>
> Dear listers,
>
> This might look like a quick question for most of you out there.
>
> My question is related to dynamic SQL. I'm looking for ways to run a dynamic SQL which doesn't have creator-id prefixed to the table name. So far, I'm bugged by SQLCODE -104 which looks hard to get past.
>
> Here is the SQL I'm trying to execute dynamically:
>
> SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM TRD_PLM_BR_PGRP_V.
> - Prepare fails with SQLCODE -104
>
> SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM CREATOR.TRD_PLM_BR_PGRP_V.
> - Executes successfully.
>
> Thanks in advance.
> Navin Agrawal
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Vin db

Re: Creator-ids & dynamic SQLs
(in response to Vin db)
Kals,
The details are following :
PLAN - CTRU006B
PACKAGE - CTRU006
OWNER - CTRU006
QUALIFIER - CTRU006

and the creator-id of the table I'm trying to access is 'CTRU006'.

Let me check the DYNAMICRULES(BIND) option.

Thanks
Navin Agrawal




Teldb2kals <[login to unmask email]> wrote:
Hi Navin,

What plan/package are you running the sql under ? Have you tried to bind
the plan with DYNAMICRULES(BIND) ? The default is RUN, which takes the
sqlid of the user running the sql for unqualified objects.
(More info on dynamicrules behaviour is in the command reference).

Regards,
Kals.

On Thu, 4 Dec 2003 03:50:56 -0800, Vin db wrote:

>I thought about that also, but again
>if the dynamic SQL, which is getting prepared at runtime, derives the
other bind parameters from the plan/package under which is getting
bothered, why should it not get the CREATORID too from the same
plan/package ?
>
>any thoughts ?
>
>Regards
>Navin Agrawal
>
>
>[login to unmask email] wrote:
>
>DB2 during dynamic SQL attaches the username of whomever issues the SQL as
>the creator of table
>and that is probably the reason why you get the abend.
>
>An alternative would be to create a synonym for your table and use that
>instead.
>
>Regards,
>
>Pantazis Pantazi
>Database Administrator
>
>Enterprise Systems Support
>Information Services Division
>Laiki Group, Cyprus
>Tel: +357-22812530, Fax: +357-22812583
>Email: [login to unmask email]
>
>
>|---------+---------------------------------->
>| | Navin Agrawal |
>| | |
>| | Sent by: DB2 Data Base |
>| | Discussion List |
>| | |
>| | 04/12/2003 01:26 ìì |
>| | Please respond to DB2 |
>| | Database Discussion |
>| | list at IDUG |
>| | |
>| | |
>|---------+---------------------------------->
>>--------------------------------------------------------------------------
-------------------------------|
>| |
>| To: [login to unmask email] |
>| cc: |
>| bcc: |
>| Subject: Creator-ids & dynamic SQLs |
>| |
>>--------------------------------------------------------------------------
-------------------------------|
>
>
>
>
>Dear listers,
>
>This might look like a quick question for most of you out there.
>
>My question is related to dynamic SQL. I'm looking for ways to run a
>dynamic SQL which doesn't have creator-id prefixed to the table name. So
>far, I'm bugged by SQLCODE -104 which looks hard to get past.
>
>Here is the SQL I'm trying to execute dynamically:
>SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM TRD_PLM_BR_PGRP_V.
>- Prepare fails with SQLCODE -104
>
>SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM
>CREATOR.TRD_PLM_BR_PGRP_V.
>- Executes successfully.
>
>Thanks in advance.
>Navin Agrawal
>
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Vin db

Re: Creator-ids & dynamic SQLs
(in response to Vin db)
Kals,

Now even though I'm binding with DYNAMICRULES(BIND), I'm getting the same result .. no success..

Does it point to 'USE FOR DYNAMICRULES' setting in my environment ? How does it affect this parameter ?

Thanks in advance
Navin Agrawal


Teldb2kals <[login to unmask email]> wrote:
Hi Navin,

What plan/package are you running the sql under ? Have you tried to bind
the plan with DYNAMICRULES(BIND) ? The default is RUN, which takes the
sqlid of the user running the sql for unqualified objects.
(More info on dynamicrules behaviour is in the command reference).

Regards,
Kals.

On Thu, 4 Dec 2003 03:50:56 -0800, Vin db wrote:

>I thought about that also, but again
>if the dynamic SQL, which is getting prepared at runtime, derives the
other bind parameters from the plan/package under which is getting
bothered, why should it not get the CREATORID too from the same
plan/package ?
>
>any thoughts ?
>
>Regards
>Navin Agrawal
>
>
>[login to unmask email] wrote:
>
>DB2 during dynamic SQL attaches the username of whomever issues the SQL as
>the creator of table
>and that is probably the reason why you get the abend.
>
>An alternative would be to create a synonym for your table and use that
>instead.
>
>Regards,
>
>Pantazis Pantazi
>Database Administrator
>
>Enterprise Systems Support
>Information Services Division
>Laiki Group, Cyprus
>Tel: +357-22812530, Fax: +357-22812583
>Email: [login to unmask email]
>
>
>|---------+---------------------------------->
>| | Navin Agrawal |
>| | |
>| | Sent by: DB2 Data Base |
>| | Discussion List |
>| | |
>| | 04/12/2003 01:26 ìì |
>| | Please respond to DB2 |
>| | Database Discussion |
>| | list at IDUG |
>| | |
>| | |
>|---------+---------------------------------->
>>--------------------------------------------------------------------------
-------------------------------|
>| |
>| To: [login to unmask email] |
>| cc: |
>| bcc: |
>| Subject: Creator-ids & dynamic SQLs |
>| |
>>--------------------------------------------------------------------------
-------------------------------|
>
>
>
>
>Dear listers,
>
>This might look like a quick question for most of you out there.
>
>My question is related to dynamic SQL. I'm looking for ways to run a
>dynamic SQL which doesn't have creator-id prefixed to the table name. So
>far, I'm bugged by SQLCODE -104 which looks hard to get past.
>
>Here is the SQL I'm trying to execute dynamically:
>SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM TRD_PLM_BR_PGRP_V.
>- Prepare fails with SQLCODE -104
>
>SELECT PROC_GRP_NO, BR_NO, BR_PROC_IND, BR_PROC_TS FROM
>CREATOR.TRD_PLM_BR_PGRP_V.
>- Executes successfully.
>
>Thanks in advance.
>Navin Agrawal
>
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Seibert

Re: Creator-ids & dynamic SQLs
(in response to Vin db)
Hello Navin,
No, you won't get a -104 SQLCode for a missing Creator. As others have
pointed out, -104 indicates a syntax error. The syntax does not require a
Creator. If there were no creator resolved by the executor or qualifier on
the package you should expect a -204.
Take Bernd's suggestion and find the offset of the error token using the
number in SQLERRD(5)
>David Seibert
>Compuware Corporation Database Product Architect
>[login to unmask email]
-----------------------------------------------------
>Hi Bernd,
>Apologies for that typo.. When I pasted the SQL from my sysout and addes
some comment, those 'fullstops' got added into.
>The details of -104 says something about the 'token', which probably points
to the missing creator-id(my guess).
>Regards
>Navin Agrawal




The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Vin db

Re: Creator-ids & dynamic SQLs
(in response to David Seibert)
Well, I agree to that point.

Still I'd need your expert views on that 'erratic-SQL' because, it runs perfectly in SPUFI, and also when I add CREATOR-ID in my dynamic SQL program.

thanks in advance.

Regards
Navin Agrawal
"Seibert, Dave" <[login to unmask email]> wrote:
Hello Navin,
No, you won't get a -104 SQLCode for a missing Creator. As others have
pointed out, -104 indicates a syntax error. The syntax does not require a
Creator. If there were no creator resolved by the executor or qualifier on
the package you should expect a -204.
Take Bernd's suggestion and find the offset of the error token using the
number in SQLERRD(5)
>David Seibert
>Compuware Corporation Database Product Architect
>[login to unmask email]
-----------------------------------------------------
>Hi Bernd,
>Apologies for that typo.. When I pasted the SQL from my sysout and addes
some comment, those 'fullstops' got added into.
>The details of -104 says something about the 'token', which probably points
to the missing creator-id(my guess).
>Regards
>Navin Agrawal




The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: Creator-ids & dynamic SQLs
(in response to Vin db)
Please post again the statement and the contents of the SQLCA, especially
SQLCODE, SQLSTATE and the SQLERRD vector from index 1 to 6.

Regards

Bernd



Am Sam, 06 Dez 2003 schrieben Sie:
>
> Well, I agree to that point.
>
> Still I'd need your expert views on that 'erratic-SQL' because, it runs perfectly in SPUFI, and also when I add CREATOR-ID in my dynamic SQL program.
>
> thanks in advance.
>
> Regards
> Navin Agrawal
> "Seibert, Dave" <[login to unmask email]> wrote:
> Hello Navin,
> No, you won't get a -104 SQLCode for a missing Creator. As others have
> pointed out, -104 indicates a syntax error. The syntax does not require a
> Creator. If there were no creator resolved by the executor or qualifier on
> the package you should expect a -204.
> Take Bernd's suggestion and find the offset of the error token using the
> number in SQLERRD(5)
> >David Seibert
> >Compuware Corporation Database Product Architect
> >[login to unmask email]
> -----------------------------------------------------
> >Hi Bernd,
> >Apologies for that typo.. When I pasted the SQL from my sysout and addes
> some comment, those 'fullstops' got added into.
> >The details of -104 says something about the 'token', which probably points
> to the missing creator-id(my guess).
> >Regards
> >Navin Agrawal
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Isaac Yassin

Re: Creator-ids & dynamic SQLs
(in response to Bernd Oppolzer)
Hi,
Ditto.
The -104 comes from a syntax error in the SQL statement that you build
dynamically in your program. From the information given up to now there is no
way to determine what causes the -104. For that we need the whole information.
If you can print the statement before doing the DESCRIBE that would be nice as
well.

Isaac Yassin


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
Bernd Oppolzer
Sent: Saturday, December 06, 2003 3:54 PM
To: [login to unmask email]
Subject: Re: Creator-ids & dynamic SQLs

Please post again the statement and the contents of the SQLCA, especially
SQLCODE, SQLSTATE and the SQLERRD vector from index 1 to 6.

Regards

Bernd



Am Sam, 06 Dez 2003 schrieben Sie:
>
> Well, I agree to that point.
>
> Still I'd need your expert views on that 'erratic-SQL' because, it runs
perfectly in SPUFI, and also when I add CREATOR-ID in my dynamic SQL program.
>
> thanks in advance.
>
> Regards
> Navin Agrawal
> "Seibert, Dave" <[login to unmask email]> wrote:
> Hello Navin,
> No, you won't get a -104 SQLCode for a missing Creator. As others have
> pointed out, -104 indicates a syntax error. The syntax does not require a
> Creator. If there were no creator resolved by the executor or qualifier on
> the package you should expect a -204.
> Take Bernd's suggestion and find the offset of the error token using the
> number in SQLERRD(5)
> >David Seibert
> >Compuware Corporation Database Product Architect
> >[login to unmask email]

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: Creator-ids & dynamic SQLs
(in response to Isaac Yassin)
Well, let's see if I have second sight some times :-)

Could it be that you, while you moved away the creator from your SQL string,
you have left the full stop there, by accident?

SELECT some_columns FROM creator.tablename

will develop into

SELECT some_columns FROM .tablename

which would explain the -104, in my opinion. Just a wild guess

Regards

Bernd



Am Sam, 06 Dez 2003 schrieben Sie:
> Hi,
> Ditto.
> The -104 comes from a syntax error in the SQL statement that you build
> dynamically in your program. From the information given up to now there is no
> way to determine what causes the -104. For that we need the whole information.
> If you can print the statement before doing the DESCRIBE that would be nice as
> well.
>
> Isaac Yassin
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
> Bernd Oppolzer
> Sent: Saturday, December 06, 2003 3:54 PM
> To: [login to unmask email]
> Subject: Re: Creator-ids & dynamic SQLs
>
> Please post again the statement and the contents of the SQLCA, especially
> SQLCODE, SQLSTATE and the SQLERRD vector from index 1 to 6.
>
> Regards
>
> Bernd
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm