ARCMAIN Error codes with explanation - 1003 , 8239

========================================================================

ARC 1003 Error code

Warning ARC1003:DATABASE "SYSUDTLIB" - skipped from DUMP Operation

========================================================================

Condition/Symptom:

            

While performing an archive of (DBC) ALL, the messages like the following were received:

ARCHIVING DATABASE "SYSUDTLIB"

*** Error 3523: The user does not have SELECT access to DBC.Dbase.

*** Warning ARC1003:DATABASE "SYSUDTLIB" - skipped from DUMP Operation.

Probable Cause:

             Beginning with Teradata Database version V2R6.0, an archive operation for the user DBC automatically includes an archive for database SYSUDTLIB. The user from which the above archive operation was performed did not have the necessary permissions to archive database SYSUDTLIB.

  

Solution:

            Grant the necessary permissions to the user from which the archive of user DBC will be performed. Those permissions are as follows:

Teradata Database V2R6.0 and later:

your author does not know the exact list of permissions required, but they can be determined by experimentation. For convenience during any such experiments, you might reduce the size of the job by archiving (DBC) without the "ALL" option.

Teradata Database V2R6.1 and later: 

DUMP on user DBC and database SYSUDTLIB, and

SELECT on tables DBC.Dbase and DBC.UDTInfo.

Teradata Database 12.0 and later:

DUMP on user DBC and database SYSUDTLIB.

No specific SELECT permissions are required.

=============================================================================

Restore/Copy from TD12 to TD13.10.4 / 14.0.1 / 14.10 fails with error 8239 when archive contains UDT

=============================================================================

Condition/Symptom:

           

For tables that contain User Defined Types (UDTs); restore/copy from a TD12 archive to a TD 13.10.4 / 14.0.1 / 14.10 system may fail with error 8239 although the UDT exists on the target system.

Restore/Copy detected invalid UDT condition: <UDT_name> does not exist.

The problem is due changes in the internal structure of UDT name between TD12 and TD13.10/TD14 and during the restore/copy operation, the copied UDT name fails comparison against the existing type name.

Sample case below illustrates this condition:

CREATE TYPE type1 AS DECIMAL(10,2) FINAL;

CREATE TABLE t1

       (a1  INT

       ,a2  type1);  <-- /* table contains UDT column */

INSERT INTO t1 (1 ,1.0);

/* Archive from TD12  */

08/27/2012 03:49:28  DBS LANGUAGE Standard

08/27/2012 03:49:28  DBS RELEASE 12.00.03.27

08/27/2012 03:49:28  DBS VERSION 12.00.03.27

08/27/2012 03:49:28 

08/27/2012 03:49:28  STATEMENT COMPLETED

08/27/2012 03:49:28

08/27/2012 03:49:28  archive data tables(db1), release lock, file = db1;

08/27/2012 03:49:30  UTILITY EVENT NUMBER  - 1594

08/27/2012 03:49:31  LOGGED ON    4 SESSIONS

08/27/2012 03:49:31 

08/27/2012 03:49:31  ARCHIVING DATABASE "db1"

08/27/2012 03:49:44  TABLE "t1" - 488 BYTES, 1 ROWS ARCHIVED

08/27/2012 03:49:44  "db1" - LOCK RELEASED

08/27/2012 03:49:44  DUMP COMPLETED

08/27/2012 03:49:44  STATEMENT COMPLETED

/* UDT 'type1' is already installed on TD13 target system */

help database sysudtlib;

 *** Help information returned. 154 rows.

 *** Total elapsed time was 1 second.

Table/View/Macro name          Kind Comment

------------------------------ ---- ---------------------------------------

<..>

type1                          U    ?

/* Copy to TD13.10 from TD12 archive failed */

08/27/2012 04:51:44  DBS LANGUAGE SUPPORT MODE Standard

08/27/2012 04:51:44  DBS RELEASE 13.10.04.08

08/27/2012 04:51:44  DBS VERSION 13.10.04.04

08/27/2012 04:51:44 

08/27/2012 04:51:44  STATEMENT COMPLETED

08/27/2012 04:51:44

08/27/2012 04:51:44  copy data tables(db1.t1), release lock, file =ARCHIVE;

08/27/2012 04:51:44  UTILITY EVENT NUMBER  - 282

08/27/2012 04:51:45  LOGGED ON    4 SESSIONS

08/27/2012 04:51:46  "db1"."t1" CREATED

08/27/2012 04:51:47  STARTING TO COPY TABLE "db1"."t1"

08/27/2012 04:51:48  DICTIONARY COPY COMPLETED

08/27/2012 04:51:48  *** Failure 8239:Restore/Copy detected invalid UDT

                     condition: TYPE1 does not exist.

08/27/2012 04:51:48  LOGGED OFF   7 SESSIONS

08/27/2012 04:51:48  ARCMAIN TERMINATED WITH SEVERITY 12

Probable Cause:

           

Archive and UDT

Solution:

           

Fixed Release(s)/Platform:

 TDBMS_13.10.4.13

TDBMS_13.10.5.2

TDBMS_13.10.6.1

TDBMS_14.0.1.8

TDBMS_14.0.2.1

TDBMS_14.10.0.1

Workaround:

           

In pre-migration follow the below workaround:

Exclude the tables with UDTs from the archive operation.The following query will identify any user tables with UDTs:

.width 256

select  (trim(c.databasename) || '.' || trim(c.tablename)) (title 'Tables With UDTs')

       ,trim(c.columnName) (title 'Column Name')

       ,trim(c.columnUDTName) (title 'UDT Name')

from    dbc.columns c inner join dbc.udtinfo u

   on   c.columnUDTName = u.typeName

where   c.columnUDTName is not null

  and   databasename not in ('sysudtlib')

order by databasename ,tablename ,columnUDTName;

To migrate the tables to a TD13 / TD14 system that does not have the fix, a manual approach can be taken.

1) Create the table on the target system to match the source

2) Export data to file from source system (example: fastexport)

3) Import data from file to target system (example: mload)