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; it's 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;