Tutorial: Db2 LUW to Azure SQL Managed Instance¶

-- Db2: count objects by type
SELECT
    TYPE AS object_type,
    COUNT(*) AS object_count
FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA NOT LIKE 'SYS%'
GROUP BY TYPE
UNION ALL
SELECT
    'TABLE' AS object_type,
    COUNT(*) AS object_count
FROM SYSCAT.TABLES
WHERE TABSCHEMA NOT LIKE 'SYS%' AND TYPE = 'T'
UNION ALL
SELECT
    'VIEW' AS object_type,
    COUNT(*) AS object_count
FROM SYSCAT.TABLES
WHERE TABSCHEMA NOT LIKE 'SYS%' AND TYPE = 'V'
UNION ALL
SELECT
    'INDEX' AS object_type,
    COUNT(*) AS object_count
FROM SYSCAT.INDEXES
WHERE INDSCHEMA NOT LIKE 'SYS%'
UNION ALL
SELECT
    'TRIGGER' AS object_type,
    COUNT(*) AS object_count
FROM SYSCAT.TRIGGERS
WHERE TRIGSCHEMA NOT LIKE 'SYS%'
UNION ALL
SELECT
    'SEQUENCE' AS object_type,
    COUNT(*) AS object_count
FROM SYSCAT.SEQUENCES
WHERE SEQSCHEMA NOT LIKE 'SYS%';

-- Db2: get database and table sizes
SELECT
    TABSCHEMA,
    TABNAME,
    CARD AS row_count,
    (DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE) / 1024 AS size_mb
FROM SYSCAT.TABLES
WHERE TYPE = 'T' AND TABSCHEMA NOT LIKE 'SYS%'
ORDER BY size_mb DESC;

-- Check for MQTs (Materialized Query Tables)
SELECT TABSCHEMA, TABNAME, REFRESH
FROM SYSCAT.TABLES
WHERE TYPE = 'S';  -- S = materialized query table

-- Check for BEFORE triggers
SELECT TRIGSCHEMA, TRIGNAME, TABNAME, TRIGTIME
FROM SYSCAT.TRIGGERS
WHERE TRIGTIME = 'B';  -- B = BEFORE

-- Check for DECFLOAT columns
SELECT TABSCHEMA, TABNAME, COLNAME, TYPENAME
FROM SYSCAT.COLUMNS
WHERE TYPENAME = 'DECFLOAT';

-- Check for GRAPHIC/DBCLOB columns
SELECT TABSCHEMA, TABNAME, COLNAME, TYPENAME
FROM SYSCAT.COLUMNS
WHERE TYPENAME IN ('GRAPHIC', 'VARGRAPHIC', 'DBCLOB');

-- Azure SQL MI: verify object counts match assessment
SELECT
    type_desc AS object_type,
    COUNT(*) AS object_count
FROM sys.objects
WHERE schema_id NOT IN (
    SELECT schema_id FROM sys.schemas
    WHERE name IN ('sys', 'INFORMATION_SCHEMA', 'ssma_db2')
)
AND type IN ('U', 'V', 'P', 'FN', 'IF', 'TF', 'TR', 'SQ')
GROUP BY type_desc
ORDER BY type_desc;

-- Azure SQL MI: monitor row counts during migration
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS table_name,
    SUM(p.rows) AS current_rows
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY t.schema_id, t.name
HAVING SUM(p.rows) > 0
ORDER BY current_rows DESC;

-- Example: migrate daily interest calculation job
-- Step 1: Create the job
EXEC msdb.dbo.sp_add_job
    @job_name = N'Daily_Interest_Calculation',
    @description = N'Migrated from Db2 LUW cron: /opt/batch/daily_interest.sh',
    @owner_login_name = N'sqladmin';

-- Step 2: Add the job step
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Daily_Interest_Calculation',
    @step_name = N'Calculate daily interest',
    @subsystem = N'TSQL',
    @command = N'
        BEGIN TRY
            EXEC dbo.sp_calculate_daily_interest @process_date = NULL;
            -- NULL defaults to today

            -- Log success
            INSERT INTO dbo.batch_job_log (job_name, status, completed_at)
            VALUES (''Daily_Interest_Calculation'', ''SUCCESS'', SYSDATETIME());
        END TRY
        BEGIN CATCH
            INSERT INTO dbo.batch_job_log (job_name, status, error_message, completed_at)
            VALUES (''Daily_Interest_Calculation'', ''FAILED'', ERROR_MESSAGE(), SYSDATETIME());
            THROW;
        END CATCH;
    ',
    @database_name = N'FinanceDB',
    @retry_attempts = 2,
    @retry_interval = 5;

-- Step 3: Create the schedule (daily at 2:00 AM)
EXEC msdb.dbo.sp_add_schedule
    @schedule_name = N'Daily_0200_EST',
    @freq_type = 4,        -- daily
    @freq_interval = 1,    -- every day
    @active_start_time = 020000;  -- 2:00 AM

-- Step 4: Attach schedule to job
EXEC msdb.dbo.sp_attach_schedule
    @job_name = N'Daily_Interest_Calculation',
    @schedule_name = N'Daily_0200_EST';

-- Step 5: Enable the job
EXEC msdb.dbo.sp_update_job
    @job_name = N'Daily_Interest_Calculation',
    @enabled = 1;

-- Check recent job execution history
SELECT
    j.name AS job_name,
    h.step_name,
    h.run_status,  -- 0=Failed, 1=Succeeded, 2=Retry, 3=Canceled
    h.run_date,
    h.run_time,
    h.run_duration,
    h.message
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE h.step_id = 0  -- job outcome
ORDER BY h.run_date DESC, h.run_time DESC;

-- Create linked server to another SQL Server instance
EXEC sp_addlinkedserver
    @server = N'REPORTING_SERVER',
    @srvproduct = N'',
    @provider = N'SQLNCLI',
    @datasrc = N'reporting-server.database.usgovcloudapi.net';

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N'REPORTING_SERVER',
    @useself = N'FALSE',
    @locallogin = NULL,
    @rmtuser = N'readonly_user',
    @rmtpassword = N'password';

-- Test linked server
SELECT TOP 10 * FROM REPORTING_SERVER.ReportingDB.dbo.summary_table;

-- Update all statistics after migration
EXEC sp_updatestats;

-- Identify missing indexes recommended by SQL Server
SELECT
    mig.index_group_handle,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_user_impact AS avg_improvement_percent,
    migs.user_seeks + migs.user_scans AS total_queries
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_user_impact > 50
ORDER BY migs.avg_user_impact DESC;

-- Verify Query Store is enabled
SELECT actual_state_desc, desired_state_desc
FROM sys.database_query_store_options;

-- Enable if not already
ALTER DATABASE FinanceDB SET QUERY_STORE = ON;