Hi,

I am new to db2. I have worked a bit upon db2 9 on WINORA server. And now shifted to db2 8.2 on AIX server.
I am facing problem while creating user defined type in db2 8.2, which was not the case in db2 9.
For e.g. in db2 9, I used -
CREATE TYPE strArray as VARCHAR(20) ARRAY[100];

While I come across a bit of change in command for creating type in db2 8.2, which I came to know from IBM's site itself, and here it is -
CREATE DISTINCT TYPE strArray as VARCHAR(20) ARRAY[100];

But it's not working out. I checked with Privileges (especially for CREATING TYPE) for my db2 user and db2 schema with this following information -

Authorization
The privilege set that is defined below must include at least one of the following:

The CREATEIN privilege on the schema
SYSADM or SYSCTRL authority
The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process. The specified distinct type name can include a schema name (a qualifier). However, if the schema name is not the same as the SQL authorization ID, one of the following conditions must be met:

The privilege set includes SYSADM or SYSCTRL authority.
The SQL authorization ID of the process has the CREATEIN privilege on the schema.

After giving these required privileges.. Still I am stuck up with the same problem in db2 8.2 with the following error, which is perfectly working on db2 9..

ERROR: A character, token, or clause is invalid or missing.

DB2
SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=CREATE
or replace TYPE;BEGIN-OF-STATEMENT;<create_type>, DRIVER=3.57.82
Error
Code: -104

I would highly appreciate if someone helps me out for solving my problem.

Thanks,
Prodigy 201

maybe cut and paste error
CREATE DISTICNT TYPE --> CREATE DISTINCT TYPE

It was typing mistake over here :-) but it was correct while executing the command on server.

Can anybody please help me to solve my problem?

Array datatype is not supported on DB2 8.2 for LUW.

CREATE TYPE (Array) was supported from DB2 9.5 for LUW, by referencing some PDF manuals.

According to the error message, you are trying to use "OR REPLACE" option on the create statement, which is not evident from the "actual" statements you have shown. "OR REPLACE" was not available until DB2 9.7. You should read manuals for your specific version of DB2.

Apart from arrays, it is also impossible to create simple user defined type of type varchar, too.. like -

create distinct type person as varchar(100);

And array type was not introduced in db2 8.2, is there any other way to handle this problem?

Or, if I I take all values in varchar as comma separated and want to split it in stored procedure, is there any function to split varchar value into array. For e.g.

"name1, name2, name3, name4, name5" can be split into varchar array?

Create another table to store variable number of "values".
So, handling(insert/update/delete/select) the values will be much easier than storing all values in a varchar.

Actually, I want to pass an array from java code to stored procedure as input parameter in stored procedure.

I want to check if each element in that array is already there in the desired table. If the elements are not in the table, I would like to insert these elements into the table.

If array was not introduced in db2 8.2, is there any way to handle array type in SP? Else, can we pass a string having all elements comma-separated as input parameter in stored procedure? And how to split then this string to get the all values as elements?

I thought that stored procedure is not necessary to do that requirements.
(If used a stored procedure, same complexity of code would be neccesary to construct a parameter for the procedure.)

Construct an INSERT statement into a string variable from the array in Java, then execute it dynamicaly.

The INSERT statement might look like...
Note: New lines and leading blanks in the sample code are for readability.
They are not necessary in an actual statement string(one blank to separate words is enough).

Another issue is how you populated the values in the array.

Depending on the way to populate the array,
you might want to construct the INSERT statement directly without using intermediate array.

Hi, that query you provided has worked out. Thanks a lot! :-)
But if I want to take for more than one fields how can I right for multiple fields?

Hi, that query you provided has worked out. Thanks a lot! :-)
But if I want to take for more than one fields how can I write for multiple fields?

I am taking comma separated values in String variable or Integer object to pass the values in query.
Like -
String str = new String('val1, 'val2', ........, 'val_n');

I don't know Java.
What value was in str, after the assignment?

And, to what query do you want to pass the values?


By the way,
from where came val1, val2, ..., val_n?
We might be better to go back more, to apply the following consideration.