Use the following upgrade procedure to extract and drop LONG BINARY columns created prior to the ESD8 level of IQ 12.5, then add LONG BINARY columns and load the extracted data after installing Sybase IQ 12.6 or later versions.
To upgrade existing LONG BINARY columns
Before you install Sybase IQ 12.6 or later versions, find all of the existing LONG BINARY columns, using this query:
SELECT table_name, column_name FROM SYS.SYSTABLE T, SYS.SYSCOLUMN C WHERE ((T.table_type=’BASE’ OR T.table_type=’GBL TEMP’) AND T.server_type=’IQ’) AND (T.table_id=C.table_id) AND (C.domain_id=12)
As an example, the query result is:
table_name column_name lb_tab lbin1 lb_tab lbin2
The table lb_tab contains two LONG BINARY columns, lbin1 and lbin2.
Add an integer column to store the length in bytes of each LONG BINARY column.
ALTER TABLE table-name ADD blen-column-name INT
where table-name is the name of the table that contains the LONG BINARY column and blen-column-name is the name of the column to store the byte length of the LONG BINARY column. Use the table-name value returned in step 1 and add a byte length column for each LONG BINARY column returned in step 1.
For example, the statements
ALTER TABLE lb_tab ADD lbin1_len INT; ALTER TABLE lb_tab ADD lbin2_len INT;
add new columns to store the byte lengths of the LONG BINARY columns lbin1 and lbin2 returned in step 1.
Save the byte length of each LONG BINARY column in the newly created length column and commit the update:
UPDATE table-name SET blen-column-name = BYTE_LENGTH( column-name ); COMMIT;
where table-name is the name of the table that contains the LONG BINARY column, blen-column-name is the name of the column to store the byte length of the LONG BINARY column, and column-name is the name of the LONG BINARY column.
For example, the statements
UPDATE lb_tab SET lbin1_len = BYTE_LENGTH( lbin1 ), lbin2_len = BYTE_LENGTH( lbin2 ); COMMIT;
save the byte lengths of the LONG BINARY columns lbin1 and lbin2 in the columns lbin1_len and lbin2_len, respectively.
Repeat this step for every LONG BINARY column that you are upgrading.
Extract each LONG BINARY column in each row to a different disk file.
SET TEMPORARY OPTION TEMP_EXTRACT_BINARY = ’ON’; SET TEMPORARY OPTION TEMP EXTRACT_NAME1 = ’file-name’; SELECT column-name FROM table-name WHERE ROWID(table-name) = row-id;
where file-name is the name of the file to which the LONG BINARY data is extracted, column-name is the name of the LONG BINARY column, table-name is the name of the table that contains the LONG BINARY column, and row-id is the row id of the row in table-name that contains the LONG BINARY column.
For example, to extract the LONG BINARY column lbin1 in row 1 of the example results of step 1, execute the following statements:
SET TEMPORARY OPTION TEMP_EXTRACT_BINARY = ’ON’; SET TEMPORARY OPTION TEMP_EXTRACT_NAME1 = ‘lbExtract1_1.inp’; SELECT lbin1 FROM lb_tab WHERE ROWID(lb_tab) = 1;
To extract the LONG BINARY column lbin1 in row 2 of the example results of step 1, execute the following statements:
SET TEMPORARY OPTION TEMP_EXTRACT_NAME1 = ‘lbExtract1_2.inp’; SELECT lbin1 FROM lb_tab WHERE ROWID(lb_tab) = 2;
Repeat this step for every LONG BINARY column in every row, for example, the column lbin2 in row id 1 and row id 2:
SET TEMPORARY OPTION TEMP_EXTRACT_BINARY = ’ON’; SET TEMPORARY OPTION TEMP_EXTRACT_NAME1 = ‘lbExtract2_1.inp’; SELECT lbin2 FROM lb_tab WHERE ROWID(lb_tab) = 1; SET TEMPORARY OPTION TEMP_EXTRACT_NAME1 = ‘lbExtract2_2.inp’; SELECT lbin2 FROM lb_tab WHERE ROWID(lb_tab) = 2;
For more information on the data extraction facility, see “Data extraction options” in Chapter 7, “Moving Data In and Out of Databases” of the Sybase IQ System Administration Guide.
Use a text editor to create a primary file as input to the LOAD TABLE command. The primary input file for a LONG BINARY column contains the path of the secondary file, which in this procedure is the extraction output file that contains the LONG BINARY data. Refer to “Loading large object data” for more information on using primary and secondary input files to load LONG BINARY data.
In the example of this procedure, use a text editor to create the primary file lob_lb_Extract.inp, which contains the following two rows:
lbExtract1_1.inp,lbExtract2_1.inp, lbExtract1_2.inp,lbExtract2_2.inp,
This primary input file specifies the names of the files that contain the data extracted from the columns lbin1 and lbin2 in rows 1 and 2 of table lb_tab.
Use the ALTER TABLE command to drop the LONG BINARY column.
ALTER TABLE table-name DROP column-name
where table-name is the name of the table that contains the LONG BINARY column and column-name is the name of the LONG BINARY column.
For example, drop the LONG BINARY columns lbin1 and lbin2 from the table lb_tab:
ALTER TABLE lb_tab DROP lbin1; ALTER TABLE lb_tab DROP lbin2;
Repeat this step for every LONG BINARY column after the LONG BINARY data has been extracted from the column.
After you install Sybase IQ 12.6 or later versions with the LOB component and run the ALTER DATABASE UPGRADE command as part of the installation procedure, use the ALTER TABLE command to add the LONG BINARY column.
ALTER TABLE table-name ADD column-name LONG BINARY
where table-name is the name of the table that contains the LONG BINARY column and column-name is the name of the LONG BINARY column.
Running the ALTER DATABASE UPGRADE command
as part of the Sybase IQ 12.6 or later installation is required
for using the domains BLOB and CLOB.
For example, add the LONG BINARY columns lbin1 and lbin2 to the table lb_tab:
ALTER TABLE lb_tab ADD lbin1 LONG BINARY; ALTER TABLE lb_tab ADD lbin2 LONG BINARY;
Repeat this step for every LONG BINARY column that you are upgrading.
Use the LOAD TABLE command to load the extracted data into the LONG BINARY column, then commit the transaction. Refer to “Loading large object data” for the necessary LOAD TABLE syntax.
For example, load the extracted data back into the LONG BINARY columns lbin1 and lbin2:
LOAD TABLE lb_tab ( lbin1 BINARY FILE (‘,’), lbin2 BINARY FILE (‘,’) ) FROM ‘lob_lbExtract.inp’ QUOTES OFF ESCAPES OFF ROW DELIMITED BY ‘\n’ START ROW ID 1; COMMIT;
Repeat this step for every LONG BINARY column that you are upgrading.
Update the LONG BINARY column with the correct length, then commit the update:
UPDATE table-name SET column-name = BYTE_SUBSTR( column-name,1, blen-column-name ); COMMIT;
where table-name is the name of the table that contains the LONG BINARY column you loaded in step 8, column-name is the name of the LONG BINARY column, and blen-column-name is the name of the column that contains the byte length of the LONG BINARY column (saved in step 3).
For example, update the lengths of the LONG BINARY columns lbin1 and lbin2:
UPDATE lb_tab SET lbin1 = BYTE_SUBSTR( lbin1,1,lbin1_len ), lbin2 = BYTE_SUBSTR( lbin2,1,lbin2_len ); COMMIT;
Repeat this step for every LONG BINARY column that you are upgrading.
For more information on the BYTE_SUBSTR function, see “BYTE_SUBSTR64 and BYTE_SUBSTR functions”.
Check the data you have loaded into the LONG BINARY column.
For example, the following query checks the data in the LONG BINARY column lbin1 in table lb_tab:
SELECT BYTE_LENGTH(lbin1), lbin1_len, BYTE_SUBSTR(lbin1,1,20), BYTE_SUBSTR(lbin1,(lbin1_len - 20),21) FROM lb_tab
The query result is:
64800 64800 AaPp1AaPp2AaPp3AaPp4 1fFuU2fFuU3fFuU4fFuU5 64800 64800 AaPp1AaPp2AaPp3AaPp4 1fFuU2fFuU3fFuU4fFuU5
Use the ALTER TABLE command to drop the byte length columns:
ALTER TABLE table-name DROP blen-column-name
where table-name is the name of the table that contains the reloaded LONG BINARY columns and blen-column-name is the name of the column that contains the length of the LONG BINARY column.
For example, drop the byte length columns lbin1_len and lbin2_len:
ALTER TABLE lb_tab DROP lbin1_len; ALTER TABLE lb_tab DROP lbin2_len;
The extracted and loaded LONG BINARY columns are now upgraded and ready to use.
