Solved

Admin_Copy_Schema error

Posted on 2014-03-27
15
1,470 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
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:Kent Olsen
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
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 

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:Kent Olsen
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:Kent Olsen
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

623 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