Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1563
  • Last Modified:

Admin_Copy_Schema error

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
maverickxx
Asked:
maverickxx
  • 7
  • 4
  • 3
  • +1
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
maverickxxAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
maverickxxAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
maverickxxAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
mustaccioCommented:
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
 
Tomas Helgi JohannssonCommented:
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
 
maverickxxAuthor Commented:
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
 
maverickxxAuthor Commented:
@Tomas,
yes, my user has privleages to create and dorp schemas.   SYSTOOLSPAPCE also exists.
0
 
mustaccioCommented:
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
 
maverickxxAuthor Commented:
@  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
 
mustaccioCommented:
Well, obviously whatever client you are using does not recognize "@" as the delimiter.
0
 
maverickxxAuthor Commented:
Mustaccio,

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now