How to use ‘Create index’ statement in iSeries Navigator

735 pts.
Tags:
AS/400
iSeries Navigator
Hi, I'm having trouble when I'm using the iSeries navigator to run statements for creating indexes on a table. The create table statements work fine but when I try to create an index for the same table, I get errors and can't seem to find out why. However, if I paste the same command on an AS/400 screen it works fine, but I want to use the iSeries navigator to run SQL scripts that creates the tables the indexes at the same time rather than having to paste each command in AS/400. Please help. Here is an example: Here is the create table statement that ran successfully. > CREATE TABLE schema.mantis_config_table ( config_id VARCHAR(64) NOT NULL, project_id INTEGER DEFAULT 0 NOT NULL, user_id INTEGER DEFAULT 0 NOT NULL, access_reqd INTEGER DEFAULT 0, type INTEGER DEFAULT 90, value CLOB NOT NULL, PRIMARY KEY (config_id, project_id, user_id) ); Statement ran successfully (27222 ms = 27.222 sec) ------------------------------------------------------------------------------------------------------ Here is the statement to create the index for the same table. This statement failed. > CREATE INDEX idx_config ON schema.mantis_config_table (config_id); ERROR MESSAGE: SQL State: 42704 Vendor Code: -204 Message: [SQL0204] JUDAN in QSYS type *LIB not found. Cause . . . . . : JUDAN in QSYS type *LIB was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, JUDAN is the service program that contains the function. The function will not be found unless the external name and usage name match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program. ------------------------------------------------------------------------------------------------- Processing ended because the highlighted statement did not complete successfully.

Software/Hardware used:
i Series Navigator

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Is JUDAN the user name that you are connecting under? If so, does library JUDAN exist on the system? Is JUDAN where you want the index to be created? If not, what schema (library) should it be created in? -- Tom
    125,585 pointsBadges:
    report
  • jailall101
    Yes JUDAN is my username for connecting to the db2. The name of the schema is 'schema' hence the table was created with the code 'CREATE TABLE schema.mantis_config_table'. The schema should also be created in 'schema'. I tink the problem is that the create index statement can't find the location 'schema.mantis_config_table'.
    735 pointsBadges:
    report
  • jailall101
    Well i solved the problem. It turns out i was supposed to specify the schema name before the index name as well. Like this 'CREATE INDEX schema.idx_config on schema.mantis_config_table (config_id)'. Whew!
    735 pointsBadges:
    report
  • TomLiotta
    Your original CREATE INDEX statement didn't qualify the idx_config reference. A common default connection property for iSeries Navigator SQL is called "SQL naming", and one of its characteristics is to use the user ID as the schema name for unqualified objects. That's a standard for SQL in general.   You can change your connection properties, or you can create a schema (library, or 'collection') named the same as your user ID, or you can qualify object references. You chose to qualify the index name.   Tom
    125,585 pointsBadges:
    report
  • jailall101
    Well thanks a lot for your information Tom. Very much appreciated.
    735 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following