DB2.Database

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function DB2.Database

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

This code connects to the DB2 server "myDB2Server" and retrieves all the data from the "myTable" table in the "myDB2Database" database.

The M Code behind the DB2.Database Function

Under the hood, the DB2.Database function is made up of several M functions that work together to establish a connection and retrieve data from the database.

Here is the M code that makes up the DB2.Database function:

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's break down each function:

Connect

The Connect function takes three parameters: server, database, and options. It creates an OleDb connection to the DB2 database using the connection string generated from the parameters.

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

The connection string is generated by concatenating the server name, database name, and options. An OleDb connection is then created using the connection string.

The source variable is a Table that contains the data retrieved from the database.

options

The options function takes a record of connection options and returns a string that can be used in the connection string.

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 “”,

It first combines all the options into a single record. It then transforms the record into a list of key-value pairs and concatenates them into a string separated by semicolons. If there are no options, the function returns an empty string.

Source

The Source function splits the query string into server name, database name, and options.

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

The Source variable is created by splitting the query string by commas. The first two elements are the server name and database name, while the rest are the connection options.

The Options variable transforms the connection options into a record. It does this by splitting each option by the equals sign and creating a record with the name and value.

Finally, the ConnectToDB2 function calls the Connect function with the server name, database name, and options, and returns the data retrieved from the database.

The DB2.Database function is a powerful tool for retrieving data from a DB2 database in Power Query M. Under the hood, it is made up of several M functions that work together to establish a connection and retrieve data from the database. Understanding the M code behind the function can help users troubleshoot connection issues and optimize their queries. By understanding the inner workings of Power Query M functions, users can become more proficient in using the tool to extract, transform, and load data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)