cancel
Showing results for 
Search instead for 
Did you mean: 

R3trans -d shows "Could not check DB2 collating sequence."

Former Member
0 Kudos

But, when I check the system, I have it.

I was doing the system copy of ecc60 ehp1 with database backup/restore

I tried two different SID TR2 and TR3 and both fails during R3trans -d

with same error.


       Database Configuration for Database tr3

 Description                                   Parameter   Current Value              Delayed Value
 ---------------------------------------------------------------------------------------------------------------
 Database configuration release level                    = 0x0c00
 Database release level                                  = 0x0c00

 Database territory                                      = en_US
 Database code page                                      = 1208
 Database code set                                       = UTF-8
 Database country/region code                            = 1
 Database collating sequence                             = IDENTITY_16BIT             IDENTITY_16BIT            
 Alternate collating sequence              (ALT_COLLATE) =                                                      
 Number compatibility                                    = OFF
 Varchar2 compatibility                                  = OFF
 Database page size                                      = 16384                      16384                     

 Dynamic SQL Query management           (DYN_QUERY_MGMT) = DISABLE                    DISABLE                   

 Discovery support for this database       (DISCOVER_DB) = ENABLE                     ENABLE                    

 Restrict access                                         = NO
 Default query optimization class         (DFT_QUERYOPT) = 5                          5                         
 Degree of parallelism                      (DFT_DEGREE) = 1                          1                         
 Continue upon arithmetic exceptions   (DFT_SQLMATHWARN) = NO                         NO                        
 Default refresh age                   (DFT_REFRESH_AGE) = 0                          0                         
 Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM                     SYSTEM                    
 Number of frequent values retained     (NUM_FREQVALUES) = 10                         10                        
 Number of quantiles retained            (NUM_QUANTILES) = 20                         20                        

 Decimal floating point rounding mode  (DECFLT_ROUNDING) = ROUND_HALF_EVEN            ROUND_HALF_EVEN           

 Backup pending                                          = NO

 Database is consistent                                  = YES
 Rollforward pending                                     = NO
 Restore pending                                         = NO

 Multi-page file allocation enabled                      = YES

 Log retain for recovery status                          = NO
 User exit for logging status                            = YES

 Self tuning memory                    (SELF_TUNING_MEM) = ON (Active)                ON                        
 Size of database shared memory (4KB)  (DATABASE_MEMORY) = 345600                     345600                    
 Database memory threshold               (DB_MEM_THRESH) = 10                         10                        
 Max storage for lock list (4KB)              (LOCKLIST) = AUTOMATIC(4470)            AUTOMATIC(4470)           
 Percent. of lock lists per application       (MAXLOCKS) = AUTOMATIC(98)              AUTOMATIC(98)             
 Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(27575)           AUTOMATIC(27575)          
 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(1124)            AUTOMATIC(1124)           
 Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(224)             AUTOMATIC(224)            

 Database heap (4KB)                            (DBHEAP) = AUTOMATIC(25000)           AUTOMATIC(25000)          
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 2560                       2560                      
 Log buffer size (4KB)                        (LOGBUFSZ) = 1024                       1024                      
 Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 10000                      10000                     
 Buffer pool size (pages)                     (BUFFPAGE) = 10000                      10000                     
 SQL statement heap (4KB)                     (STMTHEAP) = 5120                       5120                      
 Default application heap (4KB)             (APPLHEAPSZ) = AUTOMATIC(4096)            AUTOMATIC(4096)           
 Application Memory Size (4KB)             (APPL_MEMORY) = AUTOMATIC(40016)           AUTOMATIC(40016)          
 Statistics heap size (4KB)               (STAT_HEAP_SZ) = AUTOMATIC(15000)           AUTOMATIC(15000)          

 Interval for checking deadlock (ms)         (DLCHKTIME) = 10000                      10000                     
 Lock timeout (sec)                        (LOCKTIMEOUT) = 3600                       3600                      

 Changed pages threshold                (CHNGPGS_THRESH) = 40                         40                        
 Number of asynchronous page cleaners   (NUM_IOCLEANERS) = AUTOMATIC(3)               AUTOMATIC(3)              
 Number of I/O servers                   (NUM_IOSERVERS) = AUTOMATIC(5)               AUTOMATIC(5)              
 Index sort flag                             (INDEXSORT) = YES                        YES                       
 Sequential detect flag                      (SEQDETECT) = YES                        YES     
 Default prefetch size (pages)         (DFT_PREFETCH_SZ) = 32                         32                        

 Track modified pages                         (TRACKMOD) = YES                        YES                       

 Default number of containers                            = 1                          1                         
 Default tablespace extentsize (pages)   (DFT_EXTENT_SZ) = 2                          2                         

 Max number of active applications            (MAXAPPLS) = AUTOMATIC(40)              AUTOMATIC(40)             
 Average number of active applications       (AVG_APPLS) = AUTOMATIC(3)               AUTOMATIC(3)              
 Max DB files open per application            (MAXFILOP) = 61440                      61440                     

 Log file size (4KB)                         (LOGFILSIZ) = 16380                      16380                     
 Number of primary log files                (LOGPRIMARY) = 20                         20                        
 Number of secondary log files               (LOGSECOND) = 40                         40                        
 Changed path to log files                  (NEWLOGPATH) =                                                      
 Path to log files                                       = /db2/TR3/sapdata5/db2tr3/NODE0000/SQL00001/SQLOGDIR/ /db2/TR3/sapdata5/db2tr3/NODE0000/SQL00001/SQLOGDIR/
 Overflow log path                     (OVERFLOWLOGPATH) =                                                      
 Mirror log path                         (MIRRORLOGPATH) =                                                      
 First active log file                                   = S0000050.LOG               S0000050.LOG              
 Block log on disk full                (BLK_LOG_DSK_FUL) = YES                        YES                       
 Block non logged operations            (BLOCKNONLOGGED) = NO                         NO                        
 Percent max primary log space by transaction  (MAX_LOG) = 0                          0                         
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0                          0                         
 Group commit count                          (MINCOMMIT) = 1                          1                         
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 300                        300                       
 Log retain for recovery enabled             (LOGRETAIN) = OFF                        OFF                       
 User exit for logging enabled                (USEREXIT) = OFF                        OFF                       

 HADR database role                                      = STANDARD                   STANDARD                  
 HADR local host name                  (HADR_LOCAL_HOST) =                                                      
 HADR local service name                (HADR_LOCAL_SVC) =        
HADR remote host name                (HADR_REMOTE_HOST) =                                                      
 HADR remote service name              (HADR_REMOTE_SVC) =                                                      
 HADR instance name of remote server  (HADR_REMOTE_INST) =                                                      
 HADR timeout value                       (HADR_TIMEOUT) = 120                        120                       
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC                   NEARSYNC                  
 HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0                          0                         

 First log archive method                 (LOGARCHMETH1) = DISK:/export/TR2/log_archive/ DISK:/export/TR2/log_archive/
 Options for logarchmeth1                  (LOGARCHOPT1) =                                                      
 Second log archive method                (LOGARCHMETH2) = OFF                        OFF                       
 Options for logarchmeth2                  (LOGARCHOPT2) =                                                      
 Failover log archive path                (FAILARCHPATH) =                                                      
 Number of log archive retries on error   (NUMARCHRETRY) = 5                          5                         
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20                         20                        
 Vendor options                              (VENDOROPT) =                                                      
 Auto restart enabled                      (AUTORESTART) = ON                         ON                        
 Index re-creation time and redo index build  (INDEXREC) = SYSTEM                     SYSTEM (RESTART)          
 Log pages during index build            (LOGINDEXBUILD) = OFF                        OFF                       
 Default number of loadrec sessions    (DFT_LOADREC_SES) = 1                          1                         
 Number of database backups to retain   (NUM_DB_BACKUPS) = 12                         12                        
 Recovery history retention (days)     (REC_HIS_RETENTN) = 60                         60                        
 Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = OFF                        OFF          
 TSM management class                    (TSM_MGMTCLASS) =                                                      
 TSM node name                            (TSM_NODENAME) =                                                      
 TSM owner                                   (TSM_OWNER) =                                                      
 TSM password                             (TSM_PASSWORD) =                                                      

 Automatic maintenance                      (AUTO_MAINT) = ON                         ON                        
   Automatic database backup            (AUTO_DB_BACKUP) = OFF                        OFF                       
   Automatic table maintenance          (AUTO_TBL_MAINT) = ON                         ON                        
     Automatic runstats                  (AUTO_RUNSTATS) = ON                         ON                        
       Automatic statement statistics  (AUTO_STMT_STATS) = OFF                        OFF                       
     Automatic statistics profiling    (AUTO_STATS_PROF) = OFF                        OFF                       
       Automatic profile updates         (AUTO_PROF_UPD) = OFF                        OFF                       
     Automatic reorganization               (AUTO_REORG) = OFF                        OFF                       
 Enable XML Character operations        (ENABLE_XMLCHAR) = YES                        YES                       
 WLM Collection Interval (minutes)     (WLM_COLLECT_INT) = 0                          0                         


Edited by: Chun Sei on Feb 4, 2010 7:26 PM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Make sure that the database instance is up. Then connect to the database as user db2<sid> and issue the following SQL statement:

db2 "select count(1) from table(db_get_cfg()) as x"

Let me know what you get.

The output of the following SQL should be 1:

select count(1) from table(db_get_cfg()) as x where dbpartitionnum = CURRENT DBPARTITIONNUM and value = 'IDENTITY_16BIT' and name = 'db_collname'

The function DB_GET_CFG is a SYSPROC function and should always be present in the database.

- Sameer

Former Member
0 Kudos

I already tried

db2 grant execute on function SAPR3.DB6PMCF to SAPR3

db2 grant execute on function SAPR3.DB6PM_LST to SAPR3

db2 grant DROPIN, ALTERIN on SCHEMA "SAPTOOLS" TO USER SAPR3

db2 grant dbadm on database to user sapr3

And I still get this error below

dell204:~ # su - db2tr3

dell204:db2tr3 20> db2 connect to tr3

Database Connection Information

Database server = DB2/LINUXX8664 9.5.4

SQL authorization ID = DB2TR3

Local database alias = TR3

dell204:db2tr3 21> db2 "select count(1) from table(db_get_cfg()) as x"

SQL0440N No authorized routine named "DB_GET_CFG" of type "FUNCTION" having

compatible arguments was found. SQLSTATE=42884

dell204:db2tr3 22>

Former Member
0 Kudos

What version of the database are you running?

As user, db2<sid> run:

If version 8:

db2updv8 -d <SID>

if version 9.1:

db2updv91 -d <SID>

if version 9.5:

db2updv95 -d <SID>

After you run this, rerun the SQL I mentioned earlier and see if the function get_db_cfg exists.

- Sameer

Former Member
0 Kudos

db2updv95 -d TR3

MESSAGE: Failed to prepare statement

MESSAGE: WARNING: Failed to identify current database release level.

All db2updv95 updates will be made instead of only current fixpak specific updates.

MESSAGE: Error creating system routine : SYSPROC.DB2LK_DEP_OF

db2updv95 processing failed for database 'tr3'.

....when I force it with

db2updv95 -d TR3 -u xxx -p password -a

I still have the same error.

Former Member
0 Kudos

That is weird.... just to make sure your instance library paths are setup correctly, run

As root:

<DB2SOFTWAREINSTALLPATH>/instance/db2iupdt -u db2<sid> db2<sid>

Before you run this, make sure your instance is shutdown.

- Sameer

Former Member
0 Kudos

It looks like you picked a non unicode software component for a unicode component during sapinst.

If you pick a non unicode sw component and restore a unicode db then that gives collation exceptions.

Former Member
0 Kudos

I am using sapint for ecc60 ehp1. there is non-unicode option, I believe.

Former Member
0 Kudos

no it did not work.

Former Member
0 Kudos

Do you reinstall all the needed database licenses and please check also whether you use the correct codepage during export/import.

Regards

Olaf

Edited by: Olaf Balzer on Feb 5, 2010 8:41 AM

Frank-Martin
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

looks like your DB2 system catalog is in a weird inconsitent state.

Did you perform a database version upgrade recently?

If yes, what is your version history?

What is the output of

db2 " select * from SYSIBM.SYSVERSIONS "

Does the following statement return a row?

db2 " select routinename from syscat.routines where routinename like 'DB_GET_CFG' "

If the problem persits, you may need to contact SAP support to open a PMR at IBM.

Regards

Frank

Former Member
0 Kudos

No, I never upgraded / patched the server. It is fresh install. Here is the output.

dell204:db2tr3 36> db2 " select * from SYSIBM.SYSVERSIONS "

VERSIONNUMBER VERSION_TIMESTAMP AUTHID

-


-


-


9010700 2009-12-18-19.56.11.867476 DB2TR2

9050300 2009-12-29-22.19.45.538616 SYSIBM

2 record(s) selected.

dell204:db2tr3 37> db2 " select routinename from syscat.routines where routinename like 'DB_GET_CFG' "

ROUTINENAME

-


DB_GET_CFG

1 record(s) selected.

Former Member
0 Kudos

I did not database online backup and restore / rollforward it. Is there a way to check the codepage (ann other env. variables) from the exported image?

Former Member
0 Kudos

Did you do a system copy from a version 9.1 database engine to a version 9.5 database engine including a rollforward?

- Sameer

Edited by: Sameer Abhyankar on Feb 5, 2010 9:17 AM

Former Member
0 Kudos

Hi, Sameer

backup with 9.1 and restore/recover to 9.5 is not possible.

That thought accross my mind though.

The server backed up is an application server of db9.1

But I have only CLI connections.

That source server has 9.5 db and it is exported from 9.5 version.

and the server I am restoring is fresh installed 9.5 DB.

Former Member
0 Kudos

So could you please check the codepages in db cfg for target and source?

Regards

Olaf

Former Member
0 Kudos

Your database is not in the right state. You need to open an OSS message or PMR with IBM for this.

- Sameer

Former Member
0 Kudos

Everybody suspect the backup (or backup image) problem. I did not have the source server anylonger, so I created the new one and make the backup and then tried the failed target server.

It worked out fine, so there was nothing wrong with the target server, but backup (source server) has the problem.

I am closing the case, and thanks to all who convince me to try from different source.

Answers (1)

Answers (1)

Former Member
0 Kudos

trans.log file below----------



4 ETW000  [dev trc     ,00000]  0: name = R/3, con_id = 000000000 state = DISCONNECTED, perm = YES, reco = NO , timeout = 000, con_max = 255, con_opt = 255, occ = NO
4 ETW000                                                                              41  0.000932
4 ETW000  [dev trc     ,00000]  DB2 library successfully loaded DB2 library '/usr/sap/TR3/SYS/global/db6/LINUXX86_64/db6_clidriver/lib/libdb2.so' successfully loaded
4 ETW000                                                                           10614  0.011546
4 ETW000  [dev trc     ,00000]  RLIMIT_STACK: current=8388608, max=-1                 46  0.011592
4 ETW000  [dev trc     ,00000]  DB6 (DB2 UDB) UNICODE database interface 700.08 [opt]
4 ETW000                                                                              39  0.011631
4 ETW000  [dev trc     ,00000]  DB6 shared library (dbdb6slib) patchlevels            24  0.011655
4 ETW000  [dev trc     ,00000]    (0.2) DB6: patch collection 06/08 (note 1172220)
4 ETW000                                                                              39  0.011694
4 ETW000  [dev trc     ,00000]    (0.9) DB6: reduce calls to REG_LIST_VARIABLES (note 1232030)
4 ETW000                                                                              40  0.011734
4 ETW000  [dev trc     ,00000]    (0.10) DB6: UR cursor with CC_RELEASE attribute (note 1244963)
4 ETW000                                                                              66  0.011800
4 ETW000  [dev trc     ,00000]    (0.10) DB6: DB6_DBSL_RUNSTATS_V8 with sampling rate (note 1245458)
4 ETW000                                                                              39  0.011839
4 ETW000  [dev trc     ,00000]    (0.11) DB6: trace warnings on setting SQL_ATTR_CHAINING_BEGIN (note 1247697)
4 ETW000                                                                              43  0.011882
4 ETW000  [dev trc     ,00000]    (0.19) DB6: patch collection 11/08 (note 1263885)
4 ETW000                                                                              38  0.011920
4 ETW000  [dev trc     ,00000]    (0.19) DB6: patch collection 11/08 (note 1264825)
4 ETW000                                                                              36  0.011956
4 ETW000  [dev trc     ,00000]    (0.21) DB6: SUBSITUTE_VALUES causes wrong estimated statement si (note 1277235)
4 ETW000                                                                              36  0.011992
4 ETW000  [dev trc     ,00000]  Supported features:                                   23  0.012015
4 ETW000  [dev trc     ,00000]  ..retrieving configuration parameters                 23  0.012038
4 ETW000  [dev trc     ,00000]  ..done                                               110  0.012148
4 ETW000  [dev trc     ,00000]  Running with UTF-8 Unicode                            26  0.012174
4 ETW000  [dev trc     ,00000]  Running with CLI driver                             2928  0.015102
4 ETW000  [dev trc     ,00000]  Sun May 17 20:46:29 2009                         5170729  5.185831
4 ETW000  [dev trc     ,00000]  DB2 client driver version '09.05.0004'                47  5.185878
4 ETW000  [dev trc     ,00000]  Connected to DB2 server type 'DB2/LINUXX8664'         64  5.185942
4 ETW000  [dev trc     ,00000]  Connected to DB2 version '09.05.0004'                 35  5.185977
4 ETW000  [dev trc     ,00000]  Connect to 'TR3' as 'SAPR3' schema 'SAPR3' o.k.; con_hdl=0 ; appl_hdl=7
4 ETW000                                                                          206077  5.392054
4 ETW000  [dev trc     ,00000]  Database code page is ok.                             41  5.392095
4 ETW000  [dev trc     ,00000]  Sun May 17 20:46:30 2009                          370335  5.762430
4 ETW000  [dev trc     ,00000]  *** ERROR in prepareStatement[dbdb6.c, 12734] CON = 0 (BEGIN)
4 ETW000                                                                              52  5.762482
4 ETW000  [dev trc     ,00000]  &+     DbSlConnectDB6( SQLExtendedPrepare 😞 [IBM][CLI Driver][DB2/LINUXX8664] SQL0440N  No authorized routine named "DB_
4 ETW000                                                                              47  5.762529
4 ETW000  [dev trc     ,00000]  &+     GET_CFG" of type "FUNCTION" having compatible arguments was found.  SQLSTATE=42884
4 ETW000                                                                              40  5.762569
4 ETW000  [dev trc     ,00000]  &+
4 ETW000                                                                              39  5.762608
4 ETW000  [dev trc     ,00000]  &+     SELECT COUNT(*) FROM TABLE( DB_GET_CFG() ) AS X WHERE DBPARTITIONNUM = CURRENT DBPARTITIONNUM AND NAME = 'db_colln
4 ETW000                                                                              39  5.762647
4 ETW000  [dev trc     ,00000]  &+     ame' AND VALUE = 'IDENTITY_16BIT'
4 ETW000                                                                              38  5.762685
4 ETW000  [dev trc     ,00000]  &+       cursor type=NO_HOLD, isolation=UR, cc_release=YES, optlevel=5, degree=1, buffer_lobs=YES, op_type=40, reopt=0
4 ETW000                                                                              43  5.762728
4 ETW000  [dev trc     ,00000]  &+
4 ETW000                                                                              79  5.762807
4 ETW000  [dev trc     ,00000]  &+
4 ETW000                                                                              38  5.762845
4 ETW000  [dev trc     ,00000]  *** ERROR in prepareStatement[dbdb6.c, 12734] (END)
4 ETW000                                                                              37  5.762882
4 ETW000  [dev trc     ,00000]  Could not check DB2 collating sequence.               29  5.762911
4 ETW000  [dev trc     ,00000]  disconnected from 'TR3', con_hdl=0, appl_hdl=7    270764  6.033675
2EETW169 no connect possible: "DBMS = DB6                              --- DB2DBDFT = 'TR3'"