ca.portal.admin

Re:IDMS SQL DELETE statement issue

Discussion created by ca.portal.admin on Dec 20, 2006
Hi, List knowledge needed with error message ""*+ DB006052 T431 C-4M330:
Table name required in searched UPDATE or DELETE *+ statement.""

We have a table in which we can execute the following and get data
returned.

SELECT * FROM DEFAULT.PROBLEMS WHERE KEY_1 =3D '00001'; *+
*+ KEY_1       KEY_2       KEY_3       KEY_4       KEY_5       KEY_6
*+ -----       -----       -----       -----       -----       -----
*+ 00001       00004       <null>      <null>      <null>      <null>
*+
*+ 00001       00004       <null>      <null>      <null>      <null>
*+
*+ 00001       00004       <null>      <null>      <null>      <null>
*+
*+ 00001       00004       <null>      <null>      <null>      <null>
*+

The above data was placed into table with INSERT statment.

In trying to delete selected records with following statement an error
is returned in which we can not figure out root cause;

DELETE FROM DEFAULT.PROBLEMS WHERE KEY_1 =3D '00001';
*+ Status =3D -4       SQLSTATE =3D 42000        Messages follow:
*+ DB006052 T431 C-4M330: Table name required in searched UPDATE or
DELETE

*+ statement.

The message.

DB006052 C<sqlcode>M<module-number>: Table name required in searched
UPDATE or DELETE statement.
Reason: A searched UPDATE or DELETE statement must identify the target
table.
Module: IDMSSPSQ (SQLYACC)
Severity: 0

The table.

*+ Status =3D 0        SQLSTATE =3D 00000
*+   CREATE TABLE DEFAULT.PROBLEMS
*+       DEFINITION TIMESTAMP 1992-03-09-14.00.45.529150
*+       DATE CREATED 1992-03-09-13.45.31.252316   BY ADMORLT
*+       DATE LAST UPDATED 1992-03-09-13.45.31.252316   BY ADMORLT
*+     ( KEY_1                            CHARACTER(10),
*+       KEY_2                            CHARACTER(10),
*+       KEY_3                            CHARACTER(10),
*+       KEY_4                            CHARACTER(10),
*+       KEY_5                            CHARACTER(10),
*+       KEY_6                            CHARACTER(10),
*+       TEXT_1                           CHARACTER(80),
*+       TEXT_2                           CHARACTER(80),
*+       TEXT_3                           CHARACTER(80)
*+     )
*+       IN DEFAULT.DEFAULT_AREA
*+       DEFAULT INDEX ON DBKEY
*+       KEY_INDEX_1 INDEX ON ( KEY_1 )
*+       KEY_INDEX_2 INDEX ON ( KEY_2 )
*+       KEY_INDEX_3 INDEX ON ( KEY_3 )
*+       KEY_INDEX_4 INDEX ON ( KEY_4 )
*+       KEY_INDEX_5 INDEX ON ( KEY_5 )
*+       KEY_INDEX_6 INDEX ON ( KEY_6 )
*+       ;

Anyone seen this before?

Rob Klan/Cincinnati/IBM
Phone: 1-877-205-4871 (T/L: 349-2446)
ITN: 23492446
Email: rklan@us.ibm.com
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Specifying DBname for SQL-defined databases
"Some of the 16.2 new features for SQL (database cloning, SQL caching)
are controlled by specifying a DBname. However, this is not done with
the ""SET DBNAME"" command, which has no effect on SQL-defined databases.
Instead, the ""SET DICTNAME"" command (or its equivalent, such as
""CONNECT"") must be used to specify both the dictionary and the user
database. This means that the dictionary and user database must be
defined in a shared DBname.

At our shop, this is quite a problem for existing databases. All of our
dictionaries are currently defined in their own DBnames, and not
combined with user databases. Creating a new DBname with both is not
difficult. But switching over to the new DBname is. We have ""SET
DICTNAME"" or its equivalent in so many places - JCL, user profiles,
application programs, IDMS Server ODBC definitions, etc. Locating and
modifying them all would be a huge project, particularly in production.


As an alternate, I have considered adding all of the related SQL-defined
user databases to each dictionary's DBname. But I am concerned that
this may have some negative effect elsewhere.

Has anyone else experienced this problem? If so, how did you solve it?

Kay Rozeboom
State of Iowa
Information Technology Enterprise
Department of Administrative Services
Telephone: 515.281.6139 Fax: 515.281.6137
Email: Kay.Rozeboom@Iowa.Gov
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Specifying DBname for SQL-defined databases
"That is one of my concerns, Chris: whether adding a bunch of
application segments to the dictionary DBnames will affect performance.
We have tried to keep our DBnames as small as possible in the past. And
the dictionaries are used a lot......

Outcomes