Establishing a Connection

With the CData Cmdlets users can install a data module, set the connection properties, and start scripting. This section provides examples of using our DB2 Cmdlets with native PowerShell cmdlets, like the CSV import and export cmdlets.

Connecting to DB2

To connect to DB2, set these properties:

Server: The name of the server running DB2.
Port: The port the DB2 server is listening on.
Database: The name of the DB2 database.

Once you are ready to connect, choose an authentication scheme and set the appropriate properties, as described below.

Authenticating to DB2

The cmdlet supports four different schemes for authenticating to DB2: DB2 user credentials (default), encrypted user credentials, IBM Identity and Access Management (IAM) authentication, and Kerberos.

DB2 User Credentials

To authenticate using user credentials, set these properties:

AuthScheme: USRIDPWD.
User: The username of a user with access to the database.
Password: The password of a user with access to the database.

Encrypted User Credentials

If your server supports secure authentication and you want to authenticate using encrypted user credentials, set this property:

AuthScheme: EUSRIDPWD

IAM

The cmdlet supports authenticating to the DB2 server using the API key of an application that connects to it, such as Watson Query.

To authenticate using an appropriate API key, set these properties:

AuthScheme: IBMIAMAuth.
User: The IBM ID or service ID of a DB2 server user.
Password: The API key associated with the application that requires access to the DB2 database.

Kerberos

Authenticating to DB2 via Kerberos requires you to define authentication properties and choose how Kerberos should retrieve authentication tickets.

To authenticate to DB2 with Kerberos, set these properties:

AuthScheme: KERBEROS.
KerberosKDC: The Kerberos Key Distribution Center (KDC) service used to authenticate the user.
KerberosUser The principal name for the Kerberos domain controller, specified in the format host/user@realm.
KerberosSPN (optional): The Kerberos Domain Controller's Service Principal name (SPN).

Finally, to complete the security check set either of the following:

Password: The password provided for authentication to the database.
KerberosKeytabFile: The Keytab file containing your pairs of Kerberos principals and encrypted keys.

Retrieving Kerberos Tickets

Kerberos tickets are used to authenticate the requester's identity. The use of tickets instead of formal logins/passwords eliminates the need to store passwords locally or send them over a network. Users are reauthenticated (tickets are refreshed) whenever they log in at their local computer or enter kinit USER at the command prompt.

The cmdlet provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Kerberos Credential Cache Files:

Ensure that the KRB5CCNAME variable is present in your environment.
Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.
To obtain a ticket:
1. Open the MIT Kerberos Ticket Manager application.
2. Click Get Ticket.
3. Enter your principal name and password.
4. Click OK.
If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The cmdlet uses the cache file to obtain the Kerberos ticket to connect to DB2.

Note: If you would prefer not to edit KRB5CCNAME, you can use the KerberosTicketCache property to set the file path manually. After this is set, the cmdlet uses the specified cache file to obtain the Kerberos ticket to connect to DB2.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with DB2.

Enabling Cross-Realm Authentication

More complex Kerberos environments can require cross-realm authentication where multiple realms and KDC servers are used. For example, they might use one realm/KDC for user authentication, and another realm/KDC for obtaining the service ticket.

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

Creating a Connection Object

You can then use the Connect-DB2 cmdlet to create a connection object that can be passed to other cmdlets:

$conn =

Retrieving Data

After you have created a connection, you can use the other cmdlets to perform operations that you would normally expect to be able to perform against a relational database. The Select-DB2 cmdlet provides a native PowerShell interface for retrieving data:

$results = Select-DB2 -Connection $conn -Table ""Sample"."DB2INST1".Books" -Columns @("Id, Author") -Where "Category='US'"

The Invoke-DB2 cmdlet provides an SQL interface. This cmdlet can be used to execute an SQL query via the Query parameter.

Piping Cmdlet Output

The cmdlets return row objects to the pipeline one row at a time. The following line exports results to a CSV file:

Select-DB2 -Connection $conn -Table "Sample"."DB2INST1".Books -Where "Category = 'US'" | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\my"Sample"."DB2INST1".BooksData.csv -NoTypeInformation

You will notice that we piped the results from Select-DB2 into a Select-Object cmdlet and excluded some properties before piping them into an Export-CSV cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each row object in the result set, and we do not necessarily want that information in our CSV file.

However, this makes it easy to pipe the output of one cmdlet to another. The following is an example of converting a result set to JSON:

 
PS C:\> $conn  = 
PS C:\> $row = Select-DB2 -Connection $conn -Table ""Sample"."DB2INST1".Books" -Columns (Id, Author) -Where "Category = 'US'" | select -first 1
PS C:\> $row | ConvertTo-Json
{
  "Connection":  {

  },
  "Table":  ""Sample"."DB2INST1".Books",
  "Columns":  [

  ],
  "Id":  "MyId",
  "Author":  "MyAuthor"
}

Deleting Data

The following line deletes any records that match the criteria:

Select-DB2 -Connection $conn -Table "Sample"."DB2INST1".Books -Where "Category = 'US'" | Remove-DB2

Modifying Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into DB2, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\My"Sample"."DB2INST1".BooksUpdates.csv | %{
  $record = Select-DB2 -Connection $conn -Table "Sample"."DB2INST1".Books -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-DB2 -Connection $conn -Table "Sample"."DB2INST1".Books -Columns @("Id","Author") -Values @($_.Id, $_.Author) -Where "Id  = `'$_.Id`'"
  }else{
    Add-DB2 -Connection $conn -Table "Sample"."DB2INST1".Books -Columns @("Id","Author") -Values @($_.Id, $_.Author)
  }
}

Cmdlets for DB2