Skip to main content

Search

Welcome to Tibero GTS!

Issue of Missing DESC Option When Extracting FBI with tbexport

Document Type | Issue Resolution

Category | Migration

Applicable Product Versions | 6FS06, 6FS07, 7FS02

Document Number | TMITS011

 

Issue 

FBI (Function-Based Index) is a method of creating an index based on the result of applying a function or expression to a column, commonly using string processing functions such as UPPER, SUBSTR, TRIM (including cases where double quotes "" are used).
When extracting such FBI indexes as scripts using tbexport, even if a column containing double quotes ("") in the function is created with the DESC (descending order) option, the extracted script omits the DESC attribute.
 
For example, an index created as SUBSTR("TM_NO",5,8) DESC as shown below:
SQL> create index tibero1.IDX_tbl_test_01 ON tibero1.tbl_test (
PLANT_CD ASC,
SUBSTR("TM_NO",5,8) DESC
)
LOGGING
PCTFREE 10
INITRANS 2;

Index 'TIBERO1.IDX_tbl_test_01' created.
 
After extracting with tbexport, checking the log and script shows that DESC is missing and only SUBSTR("TM_NO",5,8) appears.
$ tbexport username=sys password=tibero sid=tibero file=test.dat log=test.log table=tibero1.tbl_test port=9292 script=y

$ vi test.log


--     exporting indexes


CREATE INDEX "IDX_PROD_MA_06" ON "PROD_MA" (
        "PLANT_CD" ASC,
        SUBSTR("TM_NO",5,8)
)
LOGGING
TABLESPACE "USR"
NOPARALLEL
PCTFREE 10
INITRANS 2
/
 
 
 
 

Cause

There was a logic error due to missing exception handling for double quotes ("") within the Function-Based Index code, causing failure to correctly extract the DESC (descending) option even if it was included in the index.
 
 
 
 

Solutions 

Apply patch 287049d_expimp to resolve the issue.

Caution
Apply the patch through technical support provided by Tmax Tibero.
This patch is included in the patch set, so the issue will also be resolved when upgrading with the patch set binary.
 

Example of Correct Output

  • SUBSTR("TM_NO",5,8) DESC
$ vi test.log

--     exporting indexes


CREATE INDEX "IDX_PROD_MA_06" ON "PROD_MA" (
        "PLANT_CD" ASC,
        SUBSTR("TM_NO",5,8) DESC
)
LOGGING
TABLESPACE "USR"
NOPARALLEL
PCTFREE 10
INITRANS 2
/
 
 

 

 

Related to