In this article, we will dive into the M code behind the DB2.Database function and understand how it works.

Understanding the DB2.Database Function

The DB2.Database function is used to connect to a DB2 database and retrieve data. The function takes three parameters:

1. Server: The name of the DB2 server.

2. Database: The name of the DB2 database.

3. Options: A record that contains additional connection options.

Here is an example of how to use the DB2.Database function in Power Query M:

let

Source = DB2.Database(“myDB2Server”, “myDB2Database”, [Query=”SELECT FROM myTable”])

in

Source

let

Connect = (server, database, options) =>

let

connectionString = “Provider=IBMDADB2;Database=” & database & “;Hostname=” & server & “;” & options,

connection = OleDb.DataSource(connectionString),

source = Table.FromColumns(

{connection{[Schema=””,Item=””]}[Data]},

Table.ColumnNames(connection{[Schema=””,Item=””]}[Data])

)

in

source,

options = (options) =>

let

optionsRecord = Record.Combine(options),

optionsList = List.Transform(Record.ToList(optionsRecord), each _ & “=” & optionsRecord{_}),

optionsString = Text.Combine(optionsList, “;”)

in

if optionsString <> “” then “;” & optionsString else “”,

Source = Text.Split(#”Query”, “,”),

Query = Text.Combine(List.Skip(Source), “,”),

DB2Server = Source{0},

DB2Database = Source{1},

Options = Record.Combine(

List.Transform(

List.Skip(Source, 2),

each Record.FromList(

Text.Split(_, “=”),

{“Name”, “Value”}

)

)

),

ConnectToDB2 = Connect(DB2Server, DB2Database, options(Options))

in

ConnectToDB2

let

Connect = (server, database, options) =>

let

connectionString = “Provider=IBMDADB2;Database=” & database & “;Hostname=” & server & “;” & options,

connection = OleDb.DataSource(connectionString),

source = Table.FromColumns(

{connection{[Schema=””,Item=””]}[Data]},

Table.ColumnNames(connection{[Schema=””,Item=””]}[Data])

)

in

source

let

options = (options) =>

let

optionsRecord = Record.Combine(options),

optionsList = List.Transform(Record.ToList(optionsRecord), each _ & “=” & optionsRecord{_}),

optionsString = Text.Combine(optionsList, “;”)

in

if optionsString <> “” then “;” & optionsString else “”,

let

Source = Text.Split(#”Query”, “,”),

Query = Text.Combine(List.Skip(Source), “,”),

DB2Server = Source{0},

DB2Database = Source{1},

Options = Record.Combine(

List.Transform(

List.Skip(Source, 2),

each Record.FromList(

Text.Split(_, “=”),

{“Name”, “Value”}

)

)

),

ConnectToDB2 = Connect(DB2Server, DB2Database, options(Options))

in

ConnectToDB2