Using an Older CoSort Version?
Support Termination Dates

Since the initial release of CoSort 1978, IRI has provided technical support for every version for at least five years. As per the announcements in IRI's Q1'2004, Q1'2006, Q1'2008, and Q1'2010 CoSort Journal newsletters, here is updated notification of version support end points:

Support for v8.2.x
First released on August, 2004, CoSort versions 8.2.1 through 8.2.3 no longer shipped after December 31, 2009, and were not officially supported after December 31, 2010.

Support for v9.1.x
First released in April, 2007, CoSort versions 9.1.1 through 9.1.3 will no longer ship after December 31, 2012, and will not be officially supported after December 31, 2013.


Support for v9.5.x
First released in April, 2011, CoSort version 9.5.1 and what is likely to follow through version 10.x will continue to ship through December 31, 2014, but not be officially supported after December 31, 2016.


"No longer officially supported" means that these versions will no longer ship and that, while IRI may sometimes provide a solution or workaround to a problem, no code modifications to those releases will be possible. IRI recommends that you upgrade CoSort when practical, keep your maintenance status current, and contact support@iri.com with any questions or concerns.


New Offline Reorg Solution for Big Data
IRI Workbench to Speed Unload-Order-Reload Jobs

IRI's last newsletter (Q4'2011) discussed the value of database reorganization for increasing table space and improving query response times. In comparing reorg methods, it was noted that offline (external) reorgs are faster:

"With this method, data is exported from the database into a temporary file (unload). The database objects will be set back up based on the extract, typically re-ordered (sort). They are then returned to the same tablespace (load), where indexes are restored implicitly (rebuild). IRI's FACT will rapidly unload the table to a portable flat file CoSort sorts on the primary index key of the reorganized table. With this approach, other transformation and reporting operations can also occur in the same pass, and the database can remain on-line. Pre-sorted, direct path loads also bypass the sort (overhead) of the database loader."

In the next IRI Workbench release, users will be able to front-end the interaction between FACT (unload), CoSort (sort), and major database loaders for populating one or more tables in the same or different database. The wizard will facilitate the specification and execution of multiple, large-scale reorgs, and prelude IRI's delivery of end-to-end data integration and ETL operations for big data.

Superior performance results will be seen at both the individual operation level:
  • Parallel EXTRACTION using native database drivers
  • Multi-threaded and combined data TRANSFORMATIONs using CoSort's SortCL engine
  • Pre-sorted LOADs using the database's native bulk load utility
and overall, when the pieces run together outside the database layer.

If you have any questions about this feature, or IRI's product roadmap for very large database and high-volume data warehouse environments, please contact info@iri.com.


FieldShield Development Update
Multi-Table Specification

After a successful beta period that resulted in valuable feedback, IRI is now moving FieldShield v2 into general release in the IRI Workbench environment. FieldShield v2 adds FIPS-compliant OpenSSL encryption, 3DES and AES-128 encryption, plus the ability to randomize, hash, and mask sensitive columns with custom characters.

After v2, IRI will add another significant feature: the ability to specify similar protections to a common field across multiple tables at once. This ergonomic breakthrough will allow users to apply a single privacy protection profile on the whole database, eliminating the guess work and metadata impact analyses associated with multiple feeds.

IRI anticipates updating FieldShield users with this feature, plus the ability to handle tables with LOB columns, before the third quarter. If you have any questions,
specific feature/function requests for FieldShield, or would like to test the product to safeguard personally-identifying information in your tables or flat file, please contact fieldshield@iri.com.


Spotlight on China
IRI Software in Shanghai


IRI's newest partner in China will license and support CoSort, FACT, FieldShield, RowGen, and future IRI offerings from Shanghai. Advanced Analytic Service (AAS) Co., Ltd. is an information technology consultancy delivering business intelligence, data warehouse, data integration, and performance management solutions, as well as a full complement of technical services.

AAS will become a member of IRI's global support network and combine IRI software with services to solution providers, system integrators and end-users at large and medium enterprises in mainland China. The initial market will be financial services institutions (FSIs) who need big data ETL acceleration, data franchising, data-centric protection, and legacy sort and data migration.

More information on, and contact details for, AAS in Chinese and English is available at their web site, www.analyticservice.net.



Tech Tip -  Handling CLOB Data
Use FACT to Unload and Externalize LOBs

The rapid manipulation of sequential files and fact fables is incongruous with support for long object columns in databases. Carrying that data through CoSort and FieldShield processes would also be inefficient. But because many users of IRI software have LOBs like CLOB fields in their tables, that unstructured data needs to make it into target databases after the transformation, conversion, protection, and/or reporting on the structured data has occurred.

To move the LOB data from source to target, it must be extracted using IRI's Fast Extract (FACT) tool and loaded using these instructions.

1) Verify that the table description in the database (e.g., the 'desc' command) contains a CLOB, BLOB or Long column. Using the -t option in the FACT command as show below will display the fields:

$ fact -t oracle.ini
>FACT Version 2.3.4 for Oracle, DB2, SQL Server and Sybase
Copyright 2012, CoSort Korea, Ltd.

Reading the file <oracle.ini>.....OK
Connecting to Database.....OK
[ Column Details ]
------------------------------------------------------------------------------------
No. Name Type Size Char Precision Scale
------------------------------------------------------------------------------------
1 IDX NUMBER 11 0 10 0
2 NAM CHAR 20 20 0 0
3 DAT CLOB 0 0 0 0
------------------------------------------------------------------------------------


2) Generate a FACT .ini file that contains a LOBPATH entry, for example,

DATABASE=ORACLE
INSTANCE=AIX10G
USERID=scott
PASSWORD=tiger
QUERY=select * from lob_test
OUTFILE=test.sam
CTLFILE=test.ctl
MAXROWS=1024
OUTFORMAT=FIXED
LOBPATH=./LOB


FACT will write the CLOB data to a separate files in the following format: 
"LOBPATH/column_name/split_number/row_sequence "


3) The target database's loader control file must show the correct syntax for loading *all* of the data back properly, including the required references for loading the CLOB column back at the same time into the target table. Consider these examples:

[ORACLE]
LOAD DATA
INFILE 'test.sam'
APPEND
INTO TABLE temp_table
FIELDS TERMINATED BY ''
TRAILING NULLCOLS
(
"IDX" POSITION(1:11) INTEGER EXTERNAL,
"NAM" POSITION(12:31),
lob__3 FILLER CHAR TERMINATED BY WHITESPACE,
"DAT" LOBFILE(lob__3) TERMINATED BY EOF
)


[DB2]
IMPORT FROM test.sam OF ASC \
MODIFIED BY LOBSINFILE \
METHOD L \
( \
1 11, \
12 31, \
32 61 \
) \
COMMITCOUNT 1000 \
INSERT \
INTO temp_table

 
To force the FACT-created loader control file to include the required references to the CLOB data, add the following lines in the FACT .ini (configuration) file:
LOADDATABASE=ORACLE or DB2
LOADTABLE=target_table_name


If you have specific questions about how to unload and reload CLOB, LOB or Long data using FACT please email fact@iri.com.