| Siebel SQLs/Error Messages > Siebel Remote: GenNewDB fails with SBL-GDB-00004 and 'Syntax error' for Sequence-based custom extension column
Siebel Remote: GenNewDB fails with SBL-GDB-00004 and 'Syntax error' for Sequence-based custom extension column
APPLIES TO:
Siebel CRM - Version 8.1.1.8 [23012] and later
Information in this document applies to any platform.
SYMPTOMS
Siebel Remote - on version 8.1.1.8 [23012]:
On a Siebel Test environment, Generate New Database Template (GenNewDB) task is failing with error:
"SBL-GDB-00004: Error in Main function."
Detailed errors from log:
SQLError Statement 0 00000006541b140d:0 2014-09-19 15:00:08 SQL Statement:
create table CX_STC_SEQ_GEN (
"VOBB_SEQUENCE_GEN" numeric(300,0) primary key default autoincrement,
"ROW_ID" varchar(45) not null,
"CREATED" timestamp default current timestamp not null,
"CREATED_BY" varchar(45) not null,
...)
;
DBCLog DBCLogError 1 00000006541b140d:0 2014-09-19 15:00:08 [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error near '300' on line 2
GenericLog GenericError 1 00000006541b140d:0 2014-09-19 15:00:08 SQL Message, 37000: [Siebel Database][ODBC Driver][Adaptive Server Anywhere]Syntax error near '300' on line 2
...
GenericLog GenericError 1 00000006541b140d:0 2014-09-19 15:00:09 Error creating SQL Anywhere database template file (UTLOdbcExecDirectDDL table).
GenericLog GenericError 1 00000006541b140d:0 2014-09-19 15:00:09 Error in MainFunction (CreateDbTemplateFile)
GenericLog GenericError 1 00000006541b140d:0 2014-09-19 15:00:09 (gennewdb.cpp (610) err=524292 sys=2) SBL-GDB-00004: Error in Main function.
Impact of the issue: Unless GenNewDB is completing with success, the Database Extract (DbXtract) for Mobile or Developer users could not be executed in that environment.
CAUSE
The explanation for this GenNewDB issue:
- on that custom extenstion table CX_STC_SEQ_GEN the column "VOBB_SEQUENCE_GEN" was defined in Tools as Sequence-based (having a related SEQUENCE_OBJECT="STC_VOBB_SEQ") - so that would generate auto-incrementing sequence values for new records created.
- but that was incorrectly defined as 'Varchar' Physical Type (PHYSICAL_TYPE="V"), while the expected type for such sequences would be Numeric; furthermore that has Length = 100 on server DB.
- since on the Local (SQL Anywhere) DBF template the implementation for sequences is relying on that "autoincrement" property, all such columns defined on server as Varchar would be converted actually into Numeric by GenNewDB;
- for Local database template generated, due to current UTF8 implementation, server columns' definitions are multiplied (3x) to ensure accommodating all values in this particular DB format (SQL Anywhere)
- it is therefore expected to see in GenNewDB log that VOBB_SEQUENCE_GEN column appearing in the CREATE TABLE statement as "numeric(300,0)", but that unfortunately goes beyond the supported limit for Numeric datatype for the SQL Anywhere database.
SOLUTION
In order to resolve this issue, following steps were applied:
1- Ask the Developer team to correct that "VOBB_SEQUENCE_GEN" column definition to some smaller length - i.e. Numeric(15) or Numeric(22).
(for instance - one can check how are configured some similar columns: S_AUDIT_ITEM.ITEM_IDEN_NUM, S_DOCK_TXN_LOG.TXN_ID, S_ASGN_LB_DATA.SERIAL_NUM that are set to use Sequence Objects).
2- Apply and Activate schema changes on the Repository;
3- Stop Siebel Server service, locate and (re-)move the diccache.dat and dicdata.dat from the [SIEBEL_ROOT]/siebsrvr/bin directory (- a backup copy of those could be kept until seeing those are regenerated);
4- Restart Siebel server so diccache.dat file would be recreated; (dicdata.dat would be only generated when running GenNewDB task).
5- Retry now the GenNewDB task and confirm if that is now completing fine.
6- If having any Mobile/Developer clients already extracted, please re-extract and re-initialize all their local DBFs after completing the above steps.
| 
