hanaUpgade1to2.sql

1. CHECK DB VERSION
  select * from m_database;

2. CHECK DB SIZE
  select now(), current_database() SID, to_decimal(sum(allocated_page_size/1024/1024/1024),10,0) DB_SIZE_GB from M_CONVERTER_STATISTICS;

3. CHECK FOR ORPHANED PERSISTENCE FILES
  select vf.* from sys.m_table_virtual_files_ vf where vf.name like 'attribute\_%' escape '\' and vf.COLUMN_NAME='';
  --SHOULD RETURN 0 ROWS
  --IF ISSUES, PLEASE EXECUTE THE BELOW AS sidADM USER
    python cleanupExtraFiles.py --host=<Hostname> --port=3<Instancenumber>15 --user=system --passwd=<SystemUserPassword> --removeAll --schema=<SchemaName> --table=<TableName>

4. MERGE MULTI-CONTAINER ROWSTORE TABLES
  select schema_name, table_name, 'ALTER TABLE "' || schema_name || '"."' || table_name || '" reclaim data space;' as merge_statement from m_rs_tables where container_count > 1;
  --SHOULD RETURN 0 ROWS
  --IF ANY RESULTS, EXECUTE THEM SEPERATELY , Eg -> 
    ALTER TABLE "_SYS_STATISTICS"."STATISTICS_SCHEDULE" reclaim data space;

5. CHECK SPACE SEPARATION AND COLUMN STORE FORMAT
  select v.host, v.port, case when (count > 0) then 'NO' else 'YES' end IS_CATALOG_SEPARATED from m_volumes v left outer join (select host, port, count(*) count from sys.m_dev_memory_segment where dedicated_cont_id = 0 and state = 5 group by host, port) m on v.host = m.host and v.port = m.port where v.service_name in ('indexserver', 'nameserver');
  --POSSIBLE RESULTS WILL LOOK LIKE BELOW
    node1;30,101;YES
    node2;30,103;YES

6. CHECK FOR UNFINISHED GARBAGE COLLECTION
  select * from sys.garbage_data_containers_;
  --SHOULD RETURN 0 ROWS
  --IF ANY RESULTS, EXECUTE THEM SEPERATELY

7. HOW TO CHECK AND/OR MIGRATE ALL COLUMN STORE TABLES TO THE NEW PERSISTENCE FORMAT:
  Hana2PersistenceMigrationHelper.sql
  --DOWNLOAD ABOVE FILE FROM SAP NOTE: https://launchpad.support.sap.com/#/notes/2372809 , AND RUN BELOW CMDs
  CALL HANA2_PERSISTENCE_MIGRATION_HELPER(0,?); --WILL TAKE SOMETIME TO FINISH THIS SCRIPT
  CALL HANA2_PERSISTENCE_MIGRATION_HELPER(1,?); --WILL TAKE SOMETIME TO FINISH THIS SCRIPT

8. HANA_Backups_CatalogSize

9. HANA_Backups_CatalogDeletion_CommandGenerator

10. CHECK CONSISTENCY
  CALL CHECK_TABLE_CONSISTENCY('CHECK', NULL, NULL);
  --HINT: POSSIBLE TO RUN FOR A LONG TIME, JUST WAIT

11. TRIGGER FULL DB BACKUP
  backup data all using file ('/<BackupPath>/<backupName>');

12. UPGRADE
./hdblcm --action=update --sid=<SID> --root_user=root --system_user=SYSTEM --system_user_password=<SystemUserPassword> --password=<SidadmUserPassword> --install_hostagent=off --components=server,client -b
  -- SHOULD YOU NEED ADDITIONAL COMPONENTS, YOU CAN ADD THEM IN components OPTION, Eg -> --components=server,client,afl,epmmds