DB2 自動化メモ

Last updated at 2025-02-10Posted at 2025-02-08

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

■ 前提条件

DB2 バージョン

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

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

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

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

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

日時データの条件

日時の範囲は 1990 ～ 2025 年

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

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

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

DB2 接続情報（リモート）

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

自動処理の実現方法

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 ALLn"
$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，内容格式如下：

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
}

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

DB2 自動化 メモ

=======================================

設定パラメータ

=======================================

=======================================

CSV ファイルを読み込み、各テーブル用の SQL を生成

=======================================

複数の SQL 文を UNION ALL で連結

=======================================

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 実行 → 切断 → セッション終了）

一時的なバッチファイルとして保存し、実行する

步骤 1：准备输入文件

步骤 2：编写 PowerShell 脚本

配置参数

加载DB2驱动（需预先安装IBM Data Server Client）

创建输出文件并写入标题

读取输入文件

遍历每个表

DB2 自動化メモ