Solved

Admin_Copy_Schema error

Posted on 2014-03-27
15
1,322 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:maverickxx
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 8

Accepted Solution

by:
mustaccio earned 500 total points
Comment Utility
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 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
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
Comment Utility
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
Comment Utility
@Tomas,
yes, my user has privleages to create and dorp schemas.   SYSTOOLSPAPCE also exists.
0
 
LVL 8

Expert Comment

by:mustaccio
Comment Utility
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
Comment Utility
@  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
Comment Utility
Well, obviously whatever client you are using does not recognize "@" as the delimiter.
0
 

Author Comment

by:maverickxx
Comment Utility
Mustaccio,

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now