DB2 自動化 メモ

以下は、IBM DB2 サーバー上で 200 個のテーブルそれぞれの「最古の日時」と「最新の日時」を取得するための一例です。ここでは、以下の条件を満たすソリューションを PowerShell を用いて実現する方法を示します。


---

■ 前提条件

1. DB2 バージョン

DB2 v11.1（try_cast は使用不可）



2. テーブルおよびカラム情報

各テーブルには日時を示すテキスト型のカラムが存在する

各テーブルごとに、日時カラムの名前は異なる

テーブル名と日時カラム名は、CSV（または Excel/TXT）ファイルに記載済み



3. 日時データの条件

日時の範囲は 1990 ～ 2025 年

日付のフォーマットは yyyymmdd、yymmdd、または yymm

データは手入力のため、エラー値が含まれる可能性がある

例: 純数字の誤った値（[9999]）、英字を含む値（[BL3456]）、スペース入り（[1 2312]）、桁数が異なる（[23621]）など




4. DB2 接続情報（リモート）

IP アドレス、ポート番号、DB2 のデータベース名が既に分かっている



5. 自動処理の実現方法

BAT、VBS、または PowerShell のスクリプトを用いて、

ローカルファイルからテーブル名およびカラム名を読み取り

DB2 サーバーへ接続し SQL を実行

各テーブルの最古・最新日時を取得し、ローカルファイルへ出力






---

■ SQL ロジック例

各テーブル（例：テーブル名が table1、日時カラム名が time_col1）に対して、以下のような SQL を実行して、エラー値を除外しながら日時データを DATE 型に変換し、最小・最大を取得します。

SELECT 
    'table1' AS table_name, 
    'time_col1' AS column_name,
    MIN(valid_date) AS min_date,
    MAX(valid_date) AS max_date
FROM (
    SELECT
        CASE 
            -- 8 桁の場合（yyyymmdd と想定）
            WHEN LENGTH(TRIM(time_col1)) = 8 AND REGEXP_LIKE(time_col1, '^[0-9]{8}$')
                 THEN DATE(time_col1)
            -- 6 桁の場合（yymmdd と想定。先頭に「20」を付加）
            WHEN LENGTH(TRIM(time_col1)) = 6 AND REGEXP_LIKE(time_col1, '^[0-9]{6}$')
                 THEN DATE('20' || time_col1)
            -- 4 桁の場合（yymm と想定。先頭に「20」を付加し、月は「01」と仮定）
            WHEN LENGTH(TRIM(time_col1)) = 4 AND REGEXP_LIKE(time_col1, '^[0-9]{4}$')
                 THEN DATE('20' || time_col1 || '01')
            ELSE NULL
        END AS valid_date
    FROM table1
    -- まずは、英字やスペースなどが含まれる値を除外する
    WHERE REGEXP_LIKE(time_col1, '^[0-9]+$')
) t
WHERE valid_date IS NOT NULL;

ポイント：

REGEXP_LIKE を用いて、数字のみの値に限定しています。

CASE 文で各フォーマットに応じた変換を行い、変換できた場合のみ DATE 型に変換します。

結果として、変換に成功した日時データについて、最小値（最古）と最大値（最新）を取得します。



---

■ PowerShell スクリプト例

以下の PowerShell スクリプトは、CSV ファイルからテーブル名とカラム名を読み取り、各テーブル用の SQL を生成し、DB2 サーバーへ接続して SQL を実行、最終的に結果をローカルの CSV ファイルへ出力するサンプルです。

【1】 CSV ファイル例 (tables.csv)

table_name,column_name
table1,time_col1
table2,time_col2
...

【2】 PowerShell スクリプト例

# =======================================
# 設定パラメータ
# =======================================
$DB2USER    = "your_user"            # DB2 ユーザー名
$DB2PASS    = "your_password"        # DB2 パスワード
$DB2DB      = "your_database"        # DB2 データベース名（リモートの場合は、事前に CATALOG 登録推奨）
$DB2IP      = "192.168.1.100"        # DB2 サーバーの IP アドレス
$DB2PORT    = "50000"                # DB2 サーバーのポート番号
$TableFile  = "tables.csv"           # テーブル名とカラム名が記載された CSV ファイル
$SQLFile    = "query.sql"            # 生成する SQL ファイル
$OutputFile = "result.csv"           # 最終的な出力結果ファイル

# =======================================
# CSV ファイルを読み込み、各テーブル用の SQL を生成
# =======================================
$tables = Import-Csv -Path $TableFile
$queries = @()

foreach ($t in $tables) {
    $table  = $t.table_name
    $column = $t.column_name

    $sql = @"
SELECT '$table' AS table_name, '$column' AS column_name,
       MIN(valid_date) AS min_date, MAX(valid_date) AS max_date
FROM (
    SELECT
        CASE 
            WHEN LENGTH(TRIM($column)) = 8 AND REGEXP_LIKE($column, '^[0-9]{8}$')
                 THEN DATE($column)
            WHEN LENGTH(TRIM($column)) = 6 AND REGEXP_LIKE($column, '^[0-9]{6}$')
                 THEN DATE('20' || $column)
            WHEN LENGTH(TRIM($column)) = 4 AND REGEXP_LIKE($column, '^[0-9]{4}$')
                 THEN DATE('20' || $column || '01')
            ELSE NULL
        END AS valid_date
    FROM $table
    WHERE REGEXP_LIKE($column, '^[0-9]+$')
) t
WHERE valid_date IS NOT NULL
"@
    $queries += $sql
}

# 複数の SQL 文を UNION ALL で連結
$fullSQL = $queries -join "`nUNION ALL`n"
$fullSQL | Out-File -Encoding utf8 $SQLFile
Write-Host "SQL ファイルが生成されました：$SQLFile"

# =======================================
# DB2 に接続し、SQL を実行
# =======================================
# ※ 事前に DB2 クライアントでリモート DB2 を CATALOG 登録しておくことを推奨
# 例：以下のコマンドをコマンドラインで実行
# db2 CATALOG TCPIP NODE mydbnode REMOTE $DB2IP SERVER $DB2PORT
# db2 CATALOG DATABASE $DB2DB AT NODE mydbnode
# db2 TERMINATE

# DB2 コマンドを構築（接続 → SQL 実行 → 切断 → セッション終了）
$db2Command = @"
db2 CONNECT TO $DB2DB USER $DB2USER USING $DB2PASS
db2 -tvf $SQLFile > $OutputFile
db2 DISCONNECT $DB2DB
db2 TERMINATE
"@

# 一時的なバッチファイルとして保存し、実行する
$tempBat = "run_db2.bat"
$db2Command | Out-File -Encoding ascii $tempBat

Write-Host "DB2 に接続し SQL を実行しています。しばらくお待ちください..."
& cmd.exe /c $tempBat

Write-Host "SQL の実行が完了しました。結果は $OutputFile に保存されています。"


---

■ 補足説明

DB2 接続方法について
リモート接続の場合、まず DB2 クライアント上で以下のように CATALOG コマンドを使用して接続先を登録することを推奨します。

db2 CATALOG TCPIP NODE mydbnode REMOTE 192.168.1.100 SERVER 50000
db2 CATALOG DATABASE your_database AT NODE mydbnode
db2 TERMINATE

その後、スクリプト内では CONNECT TO your_database USER your_user USING your_password を使用して接続します。

出力ファイル
上記スクリプトでは、SQL の実行結果を result.csv として保存します。必要に応じてファイル名やパスを変更してください。

その他のスクリプト例

BAT バッチファイルや VBS を使用する方法もありますが、PowerShell は CSV の読み込みや文字列操作が容易なため、推奨されます。




---

このような手順で、CSV からテーブル情報を読み取り、各テーブルの日時データを検証・変換して、DB2 上で最古・最新の日時を抽出し、最終結果をローカルファイルに出

根据需求，我们将通过 **PowerShell 脚本** 实现自动化处理。以下是完整的分步解决方案：

---

### **步骤 1：准备输入文件**
假设输入文件为 `Tables.csv`，内容格式如下：
```csv
TableName,DateColumn
Table1,COL_DATE_1
Table2,COL_TIME_2
...（共200行）
```

---

### **步骤 2：编写 PowerShell 脚本**
# 配置参数
$db2Server = "DB2_IP地址"
$db2Port = "50000"         # 默认端口为50000
$dbName = "数据库名"
$user = "用户名"
$password = "密码"
$inputFile = "Tables.csv"  # 输入文件路径
$outputFile = "Result.csv" # 输出文件路径

# 加载DB2驱动（需预先安装IBM Data Server Client）
Add-Type -Path "C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin\netf40\IBM.Data.DB2.dll"

# 创建输出文件并写入标题
"TableName,DateColumn,MinDate,MaxDate" | Out-File -FilePath $outputFile -Encoding UTF8

# 读取输入文件
$tables = Import-Csv -Path $inputFile

# 遍历每个表
foreach ($table in $tables) {
    $tableName = $table.TableName
    $dateColumn = $table.DateColumn

    # 动态生成SQL（处理不同格式和过滤错误数据）
    $sql = @"
    SELECT 
        MIN(valid_date) AS MinDate,
        MAX(valid_date) AS MaxDate
    FROM (
        SELECT
            TO_DATE(
                CASE
                    -- 检查是否为纯数字且长度合法
                    WHEN LENGTH($dateColumn) IN (4,6,8) 
                        AND TRANSLATE($dateColumn, '00000000', '0123456789') = '00000000' 
                    THEN 
                        CASE
                            -- 处理8位格式 (YYYYMMDD)
                            WHEN LENGTH($dateColumn) = 8 THEN
                                CASE
                                    WHEN SUBSTR($dateColumn,1,4) BETWEEN '1990' AND '2025'
                                        AND SUBSTR($dateColumn,5,2) BETWEEN '01' AND '12'
                                        AND SUBSTR($dateColumn,7,2) BETWEEN '01' AND 
                                            CASE 
                                                WHEN SUBSTR($dateColumn,5,2) IN ('01','03','05','07','08','10','12') THEN '31'
                                                WHEN SUBSTR($dateColumn,5,2) IN ('04','06','09','11') THEN '30'
                                                WHEN SUBSTR($dateColumn,5,2) = '02' THEN 
                                                    CASE 
                                                        WHEN (MOD(SUBSTR($dateColumn,1,4),4)=0 AND MOD(SUBSTR($dateColumn,1,4),100)<>0) 
                                                            OR MOD(SUBSTR($dateColumn,1,4),400)=0 THEN '29'
                                                        ELSE '28'
                                                    END
                                            END 
                                    THEN $dateColumn
                                    ELSE NULL
                                END
                            -- 处理6位格式 (YYMMDD)
                            WHEN LENGTH($dateColumn) = 6 THEN
                                CASE
                                    WHEN SUBSTR($dateColumn,3,2) BETWEEN '01' AND '12'
                                        AND SUBSTR($dateColumn,5,2) BETWEEN '01' AND 
                                            CASE 
                                                WHEN SUBSTR($dateColumn,3,2) IN ('01','03','05','07','08','10','12') THEN '31'
                                                WHEN SUBSTR($dateColumn,3,2) IN ('04','06','09','11') THEN '30'
                                                WHEN SUBSTR($dateColumn,3,2) = '02' THEN 
                                                    CASE 
                                                        WHEN (MOD(
                                                            CASE 
                                                                WHEN SUBSTR($dateColumn,1,2) <= '25' THEN 2000 + SUBSTR($dateColumn,1,2) 
                                                                ELSE 1900 + SUBSTR($dateColumn,1,2) 
                                                            END, 4)=0 AND MOD(
                                                            CASE 
                                                                WHEN SUBSTR($dateColumn,1,2) <= '25' THEN 2000 + SUBSTR($dateColumn,1,2) 
                                                                ELSE 1900 + SUBSTR($dateColumn,1,2) 
                                                            END, 100)<>0)
                                                            OR MOD(
                                                            CASE 
                                                                WHEN SUBSTR($dateColumn,1,2) <= '25' THEN 2000 + SUBSTR($dateColumn,1,2) 
                                                                ELSE 1900 + SUBSTR($dateColumn,1,2) 
                                                            END, 400)=0 
                                                        THEN '29' 
                                                        ELSE '28' 
                                                    END
                                            END 
                                    THEN 
                                        CASE 
                                            WHEN SUBSTR($dateColumn,1,2) <= '25' THEN '20' ELSE '19' 
                                        END || $dateColumn
                                    ELSE NULL
END
                            ELSE NULL
                        END
                    ELSE NULL  -- 过滤非数字或非法长度数据
                END,
                'YYYYMMDD'
            ) AS valid_date
        FROM $tableName
    ) AS filtered_dates
    WHERE valid_date IS NOT NULL
"@

    # 连接DB2并执行查询
    try {
        $connString = "Server=$db2Server:$db2Port;Database=$dbName;UID=$user;PWD=$password;"
        $conn = New-Object IBM.Data.DB2.DB2Connection($connString)
        $conn.Open()

        $cmd = New-Object IBM.Data.DB2.DB2Command($sql, $conn)
        $reader = $cmd.ExecuteReader()

        if ($reader.Read()) {
            $minDate = $reader["MinDate"].ToString()
            $maxDate = $reader["MaxDate"].ToString()
            "$tableName,$dateColumn,$minDate,$maxDate" | Out-File -FilePath $outputFile -Encoding UTF8 -Append
        } else {
            "$tableName,$dateColumn,No Valid Dates,No Valid Dates" | Out-File -FilePath $outputFile -Encoding UTF8 -Append
        }

        $reader.Close()
        $conn.Close()
    } catch {
        Write-Host "处理表 $tableName 时发生错误: $_"
        "$tableName,$dateColumn,Error,Error" | Out-File -FilePath $outputFile -Encoding UTF8 -Append
    }
}

Write-Host "处理完成！结果已保存到 $outputFile"
```

--- 

### **步骤 3：脚本执行说明**
1. **依赖安装**：
   - 安装 [IBM Data Server Client](https://www.ibm.com/support/pages/ibm-data-server-client-packages) ，确保 `IBM.Data.DB2.dll` 路径正确。
   - 如果无法安装客户端，可使用 ODBC 连接（需配置 DSN）。

2. **输入文件格式**：
   - 确保 `Tables.csv` 包含 `TableName` 和 `DateColumn` 列。

3. **输出文件**：
   - 结果将保存到 `Result.csv`，包含四列：表名、时间列名、最旧时间、最新时间。

---

### **关键逻辑说明**
1. **错误数据过滤**：
   - 使用 `TRANSLATE` 函数检查是否为纯数字。
   - 排除长度非4/6/8位的数据。

2. **日期转换逻辑**：
   - **8位**：直接验证年份范围及日期有效性。
   - **6位**：动态补全年份（`YY<=25` → 20XX，否则19XX），并验证日期。
   - **4位**：补全年份和日期（默认为当月第一天）。

3. **闰年处理**：
   - 根据补全后的年份判断2月天数。

---

### **注意事项**
1. **性能优化**：
   - 如果表数据量极大，建议在服务器端生成临时表存储中间结果。
   
2. **错误处理**：
   - 脚本已捕获异常并记录错误信息到输出文件。

3. **安全提示**：
   - 不要在脚本中明文存储密码，建议改用加密凭证或从外部输入。

---

执行脚本后，打开 `Result.csv` 即可查看每个表的时间范围。                 
                                   


# 输出文件标题
"TableName,DateColumn,MinDate,MinOriginal,MaxDate,MaxOriginal" | Out-File -FilePath $outputFile -Encoding UTF8

# 动态生成 SQL（核心部分）
$sql = @"
WITH valid_dates AS (
  SELECT 
    $dateColumn AS original_value,
    TO_DATE(
      CASE
        WHEN LENGTH($dateColumn) = 8 THEN
          CASE
            ...（完整日期验证逻辑）
          END
        WHEN LENGTH($dateColumn) = 6 THEN
          CASE
            ...（完整日期验证逻辑）
          END
        WHEN LENGTH($dateColumn) = 4 THEN
          CASE
            ...（完整日期验证逻辑）
          END
        ELSE NULL
      END,
      'YYYYMMDD'
    ) AS valid_date
  FROM $tableName
  WHERE 
    TRANSLATE($dateColumn, '00000000', '0123456789') = '00000000'
    AND LENGTH($dateColumn) IN (4,6,8)
)
SELECT 
  MIN(valid_date) AS MinDate,
  (SELECT original_value FROM valid_dates 
   WHERE valid_date = (SELECT MIN(valid_date) FROM valid_dates)
   FETCH FIRST 1 ROW ONLY) AS MinOriginal,
  MAX(valid_date) AS MaxDate,
  (SELECT original_value FROM valid_dates 
   WHERE valid_date = (SELECT MAX(valid_date) FROM valid_dates)
   FETCH FIRST 1 ROW ONLY) AS MaxOriginal
FROM valid_dates;
"@

# 执行查询并输出结果
try {
  $connString = "Server=$db2Server:$db2Port;Database=$dbName;UID=$user;PWD=$password;"
  $conn = New-Object IBM.Data.DB2.DB2Connection($connString)
  $conn.Open()

  $cmd = New-Object IBM.Data.DB2.DB2Command($sql, $conn)
  $reader = $cmd.ExecuteReader()

  if ($reader.Read()) {
    $minDate = $reader["MinDate"].ToString()
    $minOriginal = $reader["MinOriginal"].ToString()
    $maxDate = $reader["MaxDate"].ToString()
    $maxOriginal = $reader["MaxOriginal"].ToString()
    "$tableName,$dateColumn,$minDate,$minOriginal,$maxDate,$maxOriginal" | Out-File -FilePath $outputFile -Encoding UTF8 -Append
  } else {
    "$tableName,$dateColumn,No Valid Dates,,No Valid Dates," | Out-File -FilePath $outputFile -Encoding UTF8 -Append
  }

  $reader.Close()
  $conn.Close()
} catch {
  Write-Host "处理表 $tableName 时发生错误: $_"
  "$tableName,$dateColumn,Error,,Error," | Out-File -FilePath $outputFile -Encoding UTF8 -Append
}