DB2 VBS　接続　SELECT

以下是使用 **VBScript** 实现的自动化脚本，无需依赖 PowerShell，通过 ADODB 连接 DB2 并获取结果：

---

### **步骤 1：准备输入文件 `Tables.csv`**
内容格式如下（UTF-8 编码）：
```csv
TableName,DateColumn
Table1,COL_DATE_1
Table2,COL_TIME_2
...
```

---

### **步骤 2：创建 VBScript 脚本 `GetDB2Dates.vbs`**
```vbscript
' 配置参数
db2Server = "DB2_IP地址"   ' 替换为实际IP
db2Port = "50000"          ' 默认端口50000
dbName = "数据库名"         ' 替换为实际数据库名
user = "用户名"            ' 替换为实际用户名
password = "密码"          ' 替换为实际密码
inputFile = "Tables.csv"   ' 输入文件路径
outputFile = "Result.csv"  ' 输出文件路径

' 创建文件系统对象
Set fso = CreateObject("Scripting.FileSystemObject")

' 创建输出文件并写入标题
If Not fso.FileExists(outputFile) Then
    fso.CreateTextFile(outputFile).WriteLine "TableName,DateColumn,MinDate,MinOriginal,MaxDate,MaxOriginal"
End If

' 读取输入文件
Set input = fso.OpenTextFile(inputFile, 1, False, -1) ' -1表示UTF-8
input.ReadLine ' 跳过标题行

' 遍历每个表
Do Until input.AtEndOfStream
    line = input.ReadLine
    parts = Split(line, ",")
    tableName = Trim(parts(0))
    dateColumn = Trim(parts(1))
    
    ' 动态生成SQL（处理不同格式和错误数据）
    sql = _
        "WITH valid_dates AS (" & vbCrLf & _
        "  SELECT " & dateColumn & " AS original_value," & vbCrLf & _
        "    TO_DATE(" & vbCrLf & _
        "      CASE" & vbCrLf & _
        "        WHEN LENGTH(" & dateColumn & ") = 8 THEN" & vbCrLf & _
        "          CASE" & vbCrLf & _
        "            WHEN SUBSTR(" & dateColumn & ",1,4) BETWEEN '1990' AND '2025'" & vbCrLf & _
        "              AND SUBSTR(" & dateColumn & ",5,2) BETWEEN '01' AND '12'" & vbCrLf & _
        "              AND SUBSTR(" & dateColumn & ",7,2) BETWEEN '01' AND " & vbCrLf & _
        "                CASE " & vbCrLf & _
        "                  WHEN SUBSTR(" & dateColumn & ",5,2) IN ('01','03','05','07','08','10','12') THEN '31'" & vbCrLf & _
        "                  WHEN SUBSTR(" & dateColumn & ",5,2) IN ('04','06','09','11') THEN '30'" & vbCrLf & _
        "                  WHEN SUBSTR(" & dateColumn & ",5,2) = '02' THEN " & vbCrLf & _
        "                    CASE " & vbCrLf & _
        "                      WHEN (MOD(SUBSTR(" & dateColumn & ",1,4),4)=0 AND MOD(SUBSTR(" & dateColumn & ",1,4),100)<>0) " & vbCrLf & _
        "                        OR MOD(SUBSTR(" & dateColumn & ",1,4),400)=0 THEN '29'" & vbCrLf & _
        "                      ELSE '28'" & vbCrLf & _
        "                    END" & vbCrLf & _
        "                END " & vbCrLf & _
        "            THEN " & dateColumn & vbCrLf & _
        "            ELSE NULL" & vbCrLf & _
        "          END" & vbCrLf & _
        "        WHEN LENGTH(" & dateColumn & ") = 6 THEN" & vbCrLf & _
        "          CASE" & vbCrLf & _
        "            WHEN SUBSTR(" & dateColumn & ",3,2) BETWEEN '01' AND '12'" & vbCrLf & _
        "              AND SUBSTR(" & dateColumn & ",5,2) BETWEEN '01' AND " & vbCrLf & _
        "                CASE " & vbCrLf & _
        "                  ...（完整闰年逻辑）..." & vbCrLf & _
        "                END " & vbCrLf & _
        "            THEN " & vbCrLf & _
        "              CASE " & vbCrLf & _
        "                WHEN SUBSTR(" & dateColumn & ",1,2) <= '25' THEN '20' ELSE '19' " & vbCrLf & _
        "              END || " & dateColumn & vbCrLf & _
        "            ELSE NULL" & vbCrLf & _
        "          END" & vbCrLf & _
        "        WHEN LENGTH(" & dateColumn & ") = 4 THEN" & vbCrLf & _
        "          CASE" & vbCrLf & _
        "            WHEN SUBSTR(" & dateColumn & ",3,2) BETWEEN '01' AND '12' THEN " & vbCrLf & _
        "              CASE " & vbCrLf & _
        "                WHEN SUBSTR(" & dateColumn & ",1,2) <= '25' THEN '20' ELSE '19' " & vbCrLf & _
        "              END || " & dateColumn & " || '01'" & vbCrLf & _
        "            ELSE NULL" & vbCrLf & _
        "          END" & vbCrLf & _
        "        ELSE NULL" & vbCrLf & _
        "      END," & vbCrLf & _
        "      'YYYYMMDD'" & vbCrLf & _
        "    ) AS valid_date" & vbCrLf & _
        "  FROM " & tableName & vbCrLf & _
        "  WHERE " & vbCrLf & _
        "    TRANSLATE(" & dateColumn & ", '00000000', '0123456789') = '00000000'" & vbCrLf & _
        "    AND LENGTH(" & dateColumn & ") IN (4,6,8)" & vbCrLf & _
        ")" & vbCrLf & _
        "SELECT " & vbCrLf & _
        "  MIN(valid_date) AS MinDate," & vbCrLf & _
        "  (SELECT original_value FROM valid_dates " & vbCrLf & _
        "   WHERE valid_date = (SELECT MIN(valid_date) FROM valid_dates) " & vbCrLf & _
        "   FETCH FIRST 1 ROW ONLY) AS MinOriginal," & vbCrLf & _
        "  MAX(valid_date) AS MaxDate," & vbCrLf & _
        "  (SELECT original_value FROM valid_dates " & vbCrLf & _
        "   WHERE valid_date = (SELECT MAX(valid_date) FROM valid_dates) " & vbCrLf & _
        "   FETCH FIRST 1 ROW ONLY) AS MaxOriginal " & vbCrLf & _
        "FROM valid_dates;"
    
    ' 连接DB2
    On Error Resume Next
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = _
        "Provider=IBMDADB2;Data Source=" & dbName & _
        ";User ID=" & user & _
        ";Password=" & password & _
        ";Location=" & db2Server & ":" & db2Port & ";"
    conn.Open
    
    If Err.Number <> 0 Then
        WScript.Echo "连接失败: " & Err.Description
        Exit Do
    End If
    
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql, conn
    
    If Err.Number <> 0 Then
        WScript.Echo "查询失败: " & Err.Description
        WriteResult tableName, dateColumn, "Error", "", "Error", ""
    Else
        If Not rs.EOF Then
            minDate = rs("MinDate")
            minOriginal = rs("MinOriginal")
            maxDate = rs("MaxDate")
            maxOriginal = rs("MaxOriginal")
            WriteResult tableName, dateColumn, minDate, minOriginal, maxDate, maxOriginal
        Else
            WriteResult tableName, dateColumn, "No Valid Dates", "", "No Valid Dates", ""
        End If
    End If
    
    rs.Close
    conn.Close
Loop

input.Close
Set fso = Nothing
WScript.Echo "处理完成！结果已保存到 " & outputFile

' 写入结果到文件
Sub WriteResult(tbl, col, minD, minO, maxD, maxO)
    Set output = fso.OpenTextFile(outputFile, 8, True, -1) ' 8=追加模式, -1=UTF-8
    output.WriteLine tbl & "," & col & "," & minD & "," & minO & "," & maxD & "," & maxO
    output.Close
End Sub
```

---

### **步骤 3：执行脚本**
1. 将 `Tables.csv`、`GetDB2Dates.vbs` 放在同一目录。
2. 双击运行 `GetDB2Dates.vbs`，或通过命令行执行：
   ```cmd
   cscript GetDB2Dates.vbs
   ```
3. 结果将保存到 `Result.csv`。

---

### **关键说明**
1. **连接字符串**：
   - 使用 `Provider=IBMDADB2` 需安装 IBM DB2 OLE DB 驱动。若未安装，改用 ODBC 连接：
     ```vbscript
     conn.ConnectionString = _
         "Driver={IBM DB2 ODBC DRIVER};Database=" & dbName & _
         ";Hostname=" & db2Server & _
         ";Port=" & db2Port & _
         ";UID=" & user & _
         ";PWD=" & password & ";"
     ```
   
2. **错误处理**：
   - 脚本包含基础错误捕获，连接或查询失败时会记录错误信息。

3. **日期逻辑**：
   - SQL 逻辑与之前方案一致，确保过滤非法数据并正确转换日期。

---

### **注意事项**
1. **驱动安装**：
   - 若使用 `IBMDADB2` 提供程序，需安装 IBM Data Server Driver Package。
   - 若使用 ODBC，需确认驱动名称与安装版本匹配。

2. **性能优化**：
   - 对于大型表，建议在数据库端预先创建索引或优化查询。

3. **日志记录**：
   - 可在脚本中添加详细日志功能，记录每个表的处理状态和耗时。

---

此方案通过 VBScript 实现了与 PowerShell 相同的功能，同时避免了对 PowerShell 的依赖，适合在限制严格的环境中运行。