Convert Jbase file to Oracle XML

Hello,

When converting jBase table to Oracle the resulting Oracle table has 2 fields. One for ID (RECID) and the other for the data (in xml).

For some tables the XMl field is of type XML and for some it is BLOB.

Does anyone knows why?
How can this be configured / controlled?

This is based on the File Type in PGM.FILE.

Concat files are stored as BLOB where as H,U type files are stored as XML type.

While creating the table from jBASE (Using EB.DEV.HELPER or CREATE.FILE within T24) the data type for oracle is predefined based on the File Type field in PGM.FILE. This logic is written within the core routine.

It is not necessary if file type is H, U then XML column at Oracle end would be XMLType.
i believe it is BLOB all the time unless and until you explicitly sets its type by
TYPE=XMLORACLE

Use this one command to create table in oracle from T24. it will be BLOB table with two column, one is recid and other one for xml data.

CREATE-FILE DATA TABLE_NAME TYPE=XMLORACLE

By default in 11G R2 database with R12, The driver would use SECURE file option automatically. By means, all the new tables will be created with SECURE option enabled, C_LOB stored with SECUREFILE and if we use the environment variable in .profile.

Following is the command driver would use in order to create the table.

CREATE TABLE BINXML ( RECID VARCHAR2(255) NOT NULL, XMLRECORD XMLTYPE, CONSTRAINT PK_BINXML PRIMARY KEY(RECID) USING INDEX TABLESPACE T24DISTINDEX) TABLESPACE T24DISTDATA XMLTYPE COLUMN XMLRECORD STORE AS BINARY XML ( TABLESPACE T24DISTDATA ENABLE STORAGE IN ROW CACHE INDEX LOBI_BINXML (TABLESPACE T24DISTINDEX) )


Following are the details :

# BINARY XML VARIABLES
export JEDI_XMLDRIVER_BINARYXML=1
export JEDI_XMLDRIVER_ENABLE_ALL_COLUMNS=1
export JEDI_XMLDRIVER_ENABLE_DB_SORT=1


Official Support to ORACLE Binary XML with XQuery:
ORACLE Binary XML was introduced in 11gR1, and XQuery 1.0 standard was applied in DCD R10 onwards. However due to problems encountered by TEMENOS with the operation of using ORACLE Binary XML and XML Index this functionality had been withheld from production.
The ORACLE and TEMENOS partnership has now resolved those problems and the Binary XML, XML Index and XQuery are now functional with the R12 release. As such function based index are now not the only choice to improve query performance and XML indices can now be used to translate the jQL to XPath queries.
XML Indices can be used with both CLOB column storage as well as Binary XML column types.

Binary XML
Binary XML instead of CLOB
Binary XML is a storage model for semi-structured and unstructured XML storage introduced in Oracle 11g Release 1. (Requires ORACLE patches)
A post-parse persistent model i.e. the same format “on-disk”, “in-memory” and “across the network”. Must use Datapump for export/import.
XMLExists and XMLCast used with Xpath queries

Ø Reduced storage requirements (internal format and tokenization of tags)
Ø Reduced cpu and memory overhead associated with parsing and serializing XML
Ø Allows for piecewise updates using sliding inserts with SecureFiles
Ø Reduced network overhead – uses compact internal format to transmit data.
Ø New XML XQUERY functions ex: XMLExists and XMLCAST
Ø Can provide better query and update performance than unstructured storage

Important : Database can be reduced by > 50% due to BinaryXML “compression”


With the BINARYXML variable in place :

jsh oracle ~ -->sh
sh-4.1$ export JEDI_XMLDRIVER_BINARYXML=1
sh-4.1$ export JEDI_XMLDRIVER_ENABLE_ALL_COLUMNS=1
sh-4.1$ export JEDI_XMLDRIVER_ENABLE_DB_SORT=1
sh-4.1$ jsh

jsh oracle ~ -->sh
sh-4.1$ export JEDI_XMLDRIVER_TRACE=1
sh-4.1$ jsh
jsh oracle ~ -->
jsh oracle ~ -->CREATE-FILE DATA BINXML TYPE=XMLORACLE
[ 417 ] File BINXML created , type = XMLORACLE

XMLdriver.log

R12.101291 - 2757 - (create-file.b,11) - Fri Aug 31 11:03:33 - - BINXML - dbExecuteDirect: Start: CREATE TABLE BINXML ( RECID VARCHAR2(255) NOT NULL, XMLRECORD XMLTYPE
, CONSTRAINT PK_BINXML PRIMARY KEY(RECID) USING INDEX TABLESPACE T24DISTINDEX) TABLESPACE T24DISTDATA XMLTYPE COLUMN XMLRECORD STORE AS BINARY XML ( TABLESPACE T24DISTDATA ENABLE STORAGE IN ROW CACHE INDEX LOBI_BINXML (TABLESPACE T24DISTINDEX) )


SQL> DESC BINXML
Name Null? Type

---

---

---

RECID NOT NULL VARCHAR2(255)
XMLRECORD SYS.XMLTYPE STORAGE BINARY

SQL>


Secure Files

SECUREFILES – SECURE FILE lob storage instead of existing lob storage
Replacement for LOB storage - completely re-engineered for disk format, network protocol and redo and undo algorithms. It is designed for performance, security and disk space reduction.
Ø Optimized space management and reduced fragmentation
Ø Write-Gather Cache (WGC) buffers data (4MB) before flushing or committing to disk.
Ø Read performance enhanced by intelligent pre-fetching from disk while data is sent over the network.
Ø Network Layer that allows for high-speed data transfer between the client and server
Ø No LOB Index Contention – No High Watermark Contention
Ø New RETENTION parameter specifies policy (MAX, MIN, AUTO and NONE). Default AUTO
Ø Compression options (HIGH, MEDIUM and LOW) available for Secure File LOBs

.

hi junaid375,

What do you think if I restructure of existing 10.2.0.3 xmlrecord column in CLOB to 11.2.0.3 xmlrecord to binary xml or clob + securefile, t24 version is R8.

the conversion is mainly to test COB speed, as of know it is too long.
is it normal in our env, every MERGE DML is taken around 60 ms per execution by average.
I'll test to use CLOB securefile or binary xml speed, but need clarification regarding R8 against oracle 11.2.0.3.

thanks
ujang