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;
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;
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 -
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.
maybe cut and paste error
CREATE DISTICNT TYPE --> CREATE DISTINCT TYPE
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be
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.
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?