Solved

Admin_Copy_Schema error

Posted on 2014-03-27
15
1,419 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

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 (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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

740 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