Solved

Admin_Copy_Schema error

Posted on 2014-03-27
15
1,382 Views
Last Modified: 2014-03-31
I am trying to run a ant script which calls admin_copy_schema procedure. I am getting an error

DB2 SQL Error: SQLCODE=-469, SQLSTATE=42886, SQLERRMC=ADMIN_COPY_SCHEMA;ADMIN_COPY_SCHEMA;7;ERRO
RTABSCHEMA, DRIVER=3.53.70


DB2 version: 10.1

I am running ant script in windows, I get the same error when using squirrel

Any ideas?
0
Comment
Question by:maverickxx
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 39961687
Hi Maverick,

That's probably a parameter error to the function/procedure.

Can you post the exact statement that you're trying to execute?


Kent
0
 

Author Comment

by:maverickxx
ID: 39961723
Kent

thanks for the reply

call sysproc.admin_copy_schema ('Source schema ','Target schema', 'COPY',NULL,NULL, NULL,'ERRORSCHEMA','COPYERROR').

I get a similar error from Admin_drop_schema

I am able to run this statement from linux shell
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39961773
Hi Maverick,

I suspect that an error is occurring during procedure execution and that DB2 is unable to return the SQL status back to the caller.  

call sysproc.admin_copy_schema (
  'Source schema ',
  'Target schema',
  'COPY',
  NULL,  -- SQLCODE
  NULL,  -- SQLSTATE
  NULL,  -- ERROR_TIMESTAMP
  'ERRORSCHEMA',
  'COPYERROR').

The three parameters that I've flagged are all OUT parameters in the procedure declaration.  Since it's DB2's nature to return error codes, they probably can't be NULL.

Can you check the log file to see if an error was posted immediately before?

 DB2 SQL Error: SQLCODE=-469, SQLSTATE=42886, SQLERRMC=ADMIN_COPY_SCHEMA

Or pass valid variables that DB2 can set with the status.


Kent
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:maverickxx
ID: 39961916
Kent ,

I have tried putting values for the parameters you flagged, I am getting an error for "errortabschema" which is not null.


I also get the same error for admin_drop_schema
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39961929
Hi Maverick,

The error is is quite specific:

  http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.messages.sql.doc%2Fdoc%2Fmsql00469n.html

DB2 is attempting to write to a parameter that is declared as OUT or INOUT.

What client are you using?  How are you passing writeable variables to the procedure?
0
 

Author Comment

by:maverickxx
ID: 39961954
Hi Kent ,

I am running Ant scripts from command line in windows environment.  DB2 is on AIX .

Right now the variables are hard coded for proof of concept purposes. I have tried the call procedure on squirrel client and I get the same error. However if I invoke the call  from  a shell script , the procedure works for same parameters.


Could it be a jar issue?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39961970
Hi Maverick,

It could be.  Since DB2 is trying to write back to user defined variables, the variables must exist.  The difference in the clients/connections could be the culprit.

An easy test would be to write your own procedure that does nothing more than call the procedure from the sysproc schema.  Create all of the variables in your procedure and pass them to the system procedure.  

On return from sysproc you could write all of the variables to your own table, log them, print them, etc.  The fact that you're seeing this error suggests that DB2 is returning the status instead of throwing an exception.


Kent
0
 
LVL 8

Accepted Solution

by:
mustaccio earned 500 total points
ID: 39962570
The ERRORTABSCHEMA and ERRORTAB parameters are IN OUT, so you need to declare two variables and supply them as parameters, for the procedure to return values into them.

The DB2 CLP handles this automatically. When running the statement from a program, you'll need to take care of that yourself.

If you don't care about the return values, you can do this:

begin
 declare l_errschema varchar(128) default 'ERRORSCHEMA';
 declare l_errtab varchar(128) default 'COPYERROR';
 call sysproc.admin_copy_schema ('Source schema ','Target schema', 'COPY',
       NULL,NULL,NULL,l_errschema,l_errtab);
end

Open in new window

0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39967103
Hi!

Does your user that is issuing the ADMIN_COPY_SCHEMA command have enough privileges in all 3 schemas and does the errorschema exist ?

http://www.dba-db2.com/2013/06/move-db2-schema-objects-to-another-schema-using-admin_copy_schema.html

Make sure you have an SYSTOOLSPACE tablespace created in your database.

Regards,
    Tomas Helgi
0
 

Author Comment

by:maverickxx
ID: 39967512
mustaccio,
 
I tried to execute the procedure  code you provided  from ant script and in squirrel and I am getting < psm_semicolon> error.

 I tried using a non standard de-limiter instead of semicolon  (like @ , # etc) I am still getting psm-semicolon error
0
 

Author Comment

by:maverickxx
ID: 39967524
@Tomas,
yes, my user has privleages to create and dorp schemas.   SYSTOOLSPAPCE also exists.
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 39967613
I tried using a non standard de-limiter instead of semicolon  (like @ , # etc) I am still getting psm-semicolon error

You only need a non-standard delimiter after the final end keyword. Within the compound statement you must use the standard delimiter, the semicolon. Was that what you tried?
0
 

Author Comment

by:maverickxx
ID: 39967643
@  Mustaccio

yes I  used @ after the end statement

begin
 declare l_errschema varchar(128) default 'ERRORSCHEMA1';
 declare l_errtab varchar(128) default 'COPYERROR';
 call sysproc.admin_copy_schema ('Source schema ','Target schema', 'COPY',
       NULL,NULL,NULL,l_errschema,l_errtab);
end
@


I got the following error

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;fault 'ERRORSCHEMA1';<psm_semicolon>, DRIVER=3.53.71
SQLState:  42601
ErrorCode: -104
Error occured in:
begin
declare l_errschema varchar(128) default 'ERRORSCHEMA1'
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 39968019
Well, obviously whatever client you are using does not recognize "@" as the delimiter.
0
 

Author Comment

by:maverickxx
ID: 39968286
Mustaccio,

I was able to make it work by specifying "@" as delimiter. THanks for you help!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question