13.7 Renumbering Relative Record Numbers
You need to renumber the LANSA relative record number column when it has been created using the Auto RRNO attribute. That is, when the RRNO column is an Identity column.
The following SQL scripts are provided as an example of how to perform this renumbering task using SQL Scripts. It presumes that you are an experienced database administrator. These scripts are provided on an as is basis. No warranty is implied or given. Please test them thoroughly before using. It is strongly recommended that you backup your database before executing the script.
In these scripts OwnerName should be set to the LANSA library and TableName to the LANSA file name.
SQL Server
Use SQL Server Management Studio to connect to the database and enter this script modifying OwnerName and TableName appropriately. Also change the USE statement to the name of your database.
The statements are displayed as they are executed.
The script drops the RRNO index, drops the RRNO column from the table, adds the column back into the table (resetting the initial number to 1) and then re-creates the index.
USE [MYDATABASE]
GO
BEGIN
SET NOCOUNT ON -- Allows concatenation to work
DECLARE @OwnerName varchar(max);
DECLARE @TableName varchar(max);
DECLARE @FullName varchar(max);
DECLARE @IndexName varchar(max);
DECLARE @SQL nvarchar(max);
SET @OwnerName = 'MYLIBRARY';
SET @TableName = 'MYFILE';
SET @FullName = (@OwnerName + '.' + @TableName)
-- PRINT @FullName
SET @IndexName = @TableName + '_R';
-- PRINT @IndexName
SET @SQL = 'DROP INDEX ' + @IndexName + ' ON ' + @FullName;
PRINT @SQL
EXECUTE sp_executesql @SQL;
SET @SQL = 'ALTER TABLE ' + @FullName + ' DROP COLUMN X_RRNO';
PRINT @SQL
EXECUTE sp_executesql @SQL;
SET @SQL = 'ALTER TABLE ' + @FullName + ' ADD "X_RRNO" decimal(15,0) IDENTITY(1,1) NOT NULL';
PRINT @SQL
EXECUTE sp_executesql @SQL;
SET @SQL = 'CREATE UNIQUE NONCLUSTERED INDEX ' + @IndexName + ' ON ' + @FullName + ' ( "X_RRNO" ASC )';
PRINT @SQL
EXECUTE sp_executesql @SQL;
END
SQL Anywhere
Connect to the database and enter this script modifying OwnerName and TableName appropriately.
The script drops the RRNO index, drops the RRNO column from the table, adds the column back into the table (resetting the initial number to 1) and then re-creates the index.
BEGIN
DECLARE @@OwnerName char(128);
DECLARE @@TableName char(128);
DECLARE @@IndexName char(128);
SET @@OwnerName = 'MYLIBRARY';
SET @@TableName = 'MYFILE';
SET @@IndexName = STRING(@@TableName, '_R');
EXECUTE IMMEDIATE 'DROP INDEX ' || @@IndexName;
EXECUTE IMMEDIATE 'ALTER TABLE ' || @@OwnerName || '.' || @@TableName || ' DROP X_RRNO';
EXECUTE IMMEDIATE 'ALTER TABLE ' || @@OwnerName || '.' || @@TableName || ' ADD ("X_RRNO" decimal(15,0) NOT NULL DEFAULT autoincrement)';
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX ' || @@IndexName || ' ON ' || @@OwnerName || '.' || @@TableName || ' ( "X_RRNO" ASC ) IN "SYSTEM"';
END
Oracle
Connect to the database and enter this script modifying OwnerName and TableName appropriately.
This script is different to the other two as we need to manually re-sequence; its not a feature of dropping a column and re-creating it.
DECLARE
OwnerName varchar2(128) := 'SCOTT';
TableName varchar2(128) := 'ROB7_ORA';
FullName varchar2(128) := OwnerName || '.' || TableName;
IndexName varchar2(128) := TableName || '_R';
FullIndexName varchar2(128) := OwnerName || '.' || IndexName;
MaxRRNO NUMERIC := 0;
NextSeq NUMERIC := 0;
plsql VARCHAR2(500);
BEGIN
-- Remove Index so we can have duplicates whilst we re-number
EXECUTE IMMEDIATE 'DROP INDEX ' || IndexName;
-- Re-sequence X_RRNO
EXECUTE IMMEDIATE 'UPDATE ' || FullName || ' SET X_RRNO=ROWNUM';
-- Need to get the max RRNO back, so must use a PL/SQL Block
plsql := 'BEGIN SELECT MAX(X_RRNO) INTO :a FROM ' || FullName || '; END;';
EXECUTE IMMEDIATE plsql USING IN OUT MaxRRNO;
DBMS_OUTPUT.PUT_LINE('Maximum RRNO = ' || MaxRRNO);
IF MaxRRNO IS NULL THEN
NextSeq := 1;
ELSE
NextSeq := MaxRRNO + 1;
END IF;
-- Reset sequence to Max
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || FullName || 'X_RRNO_SEQ';
EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || FullName || 'X_RRNO_SEQ
START WITH ' || NextSeq ||
'INCREMENT BY 1';
-- Create Index now that we have unique numbers
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX ' || FullIndexName || ' ON ' || FullName || ' ( X_RRNO ASC )';
END;