Ricky

链接

RSS

RSS Link
z/OS的ftp命令控制文件格式为Unix
UTF-8文件上传到z/OS并转IBM-1388

Excel中访问DB2 for z/OS

Ricky posted @ 2013年9月27日 09:27 in Other , 1264 阅读

安装一个DB2客户端,比如Data Server或者Toad for DB2

这样就可以创建ODBC数据源

通过ODBC访问并插入工作表:

Sub GetData()
    'Define SQL query string to get the CategoryName field from
    'the Category table.
    sqlstring = "SELECT * From ..."
    'Define connection string and reference File DSN.
    connstring = "ODBC;DSN=DSNName"
    'Create QueryTable in worksheet beginning with cell A1.
    With ActiveSheet.QueryTables.Add(Connection:=connstring, _
        Destination:=Range("A1"), Sql:=sqlstring)
        .Refresh BackgroundQuery := False
    End With

End Sub

 

.Refresh BackgroundQuery := False将会等待查询完成再继续操作,否则在后台完成查询

 

另一种方式:

执行查询并将结果返回到数组,要用到RecordSet对象:


    'define connection and reference file DSN.
    strCon = "Driver={IBM DB2 ODBC DRIVER};Database=DBName;Hostname=" & serverAddress & ";Port=1234;Protocol=TCPIP;Uid=xxx;Pwd=xxx;"

    Set dbCon = CreateObject("ADODB.Connection")
    dbCon.Open strCon

    'define sql stmt
    strSql = "SELECT NAME From SYSIBM.SYSCOLUMNS WHERE TBNAME = '" & tabName & "' AND TBCREATOR = '" & schemaName & "' ORDER BY COLNO;"

    Set rstRecordset = CreateObject("ADODB.Recordset")
    rstRecordset.Open strSql, dbCon, 0, 1, 1

    ' Get records into array
    vArray = rstRecordset.GetRows
    Set rstRecordset = Nothing

    For i = LBound(vArray, 2) To UBound(vArray, 2)
        Msbbox vArray(0,i)
    Next i


    ' Clean up
    Erase vArray  

 


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter