Tutorial (DB2)

This tutorial will show you how to run Gallium Data on your machine, along with DB2. Nothing will be installed permanently, it’s all done with containers, so you can throw everything away when you’re done.

There are several versions of this tutorial: 

• this one, which uses IBM DB2 as a database,

• an equivalent tutorial for MySQL,

• one for PostgreSQL,

• one for Microsoft SQL Server,

• one for Vertica,

• one for Redis,

• one for Cassandra,

• and one for MongoDB.

The concepts are very similar, pick the database you're most familiar with.

This tutorial will take about 10 minutes, depending on your download speed. It can be run on anything that runs Docker.

Step 1: Make sure Docker is running

For this tutorial, we will use Docker (1). To verify that Docker is indeed available:

You should see some output similar to this:

Client:

 Cloud integration: v1.0.35+desktop.5

 Version:           24.0.7

 API version:       1.43

 Go version:        go1.20.10
etc...

The exact version numbers are not important -- the important thing is that Docker needs to be running. 

If you get an error message, you'll need to get Docker up and running before you can continue with this tutorial. Fortunately, there are lots of resources that can help you with that.

We’ll be starting two Docker containers , one for DB2, and one for Gallium Data. There are more repeatable ways of doing this using e.g. Docker Compose, Kubernetes or Nomad, but for this tutorial we want to make sure every component is visible and clearly understood.

We’ll be running both containers in their own Docker network, so let’s create that network. 

The response should be a long string of letters and numbers, which you can safely ignore, something like:

9ef282f6d3cce819a etc...

If you get an error, it may be because you've run another Gallium Data tutorial before, in which case you can ignore the error and carry on.

Docker is now ready, let’s move on to the next step.

Step 2: Start the two Docker containers

If this is your first time running this tutorial, note that it will download about 1.6GB of container images (almost all of which is DB2). This can take a while on slower connections.

1 - Start the DB2 database 

This image is simply the standard DB2 Community image, with a small sample database that we will use in this tutorial.

There is nothing special about this image:  Gallium Data can run with any DB2/LUW version from the last 15 years, including on-premise or in the cloud. [Side note: if you're interested in running this with DB2/z or DB2/i, please contact us, we'd love to talk to you]

Once the image is downloaded, DB2 will take 2-3 minutes to start. 

You will see a fair amount of logging scrolling by. Please be patient, DB2 will be up and ready when you see the message:

db2 => Gallium Data demo has been loaded

There will be many other logging messages, however, so you can also look for the message:

STOP    : DATABASE: GALLIUM  : DEACTIVATED: NO

which will tell you that the database is up and running.

2 - Start Gallium Data

We'll leave DB2 running in its own terminal window. Open a different terminal for the  next step.

This is the standard Gallium Data image, with a demo repository for this tutorial.

Run a DB2 query

Connect to DB2 using the CLPPLUS command line:

This will give you a DB2 command line:

CLPPlus: Version 1.6

...

SQL>

You will see some rows of data:

         ID FIRST_NAME                 LAST_NAME                               COUNTRY

----------- -------------------------- --------------------------------------- -------

          1 Andrea                     Albinioni                               AR     

          2 Bruno                      Botticelli                              BE     

          3 Charles                    Corleone                                CA     

          4 Daniella                   Durango                                 DK     

           etc...

The output may look a bit jumbled. If your terminal is wider than 80 characters, you can instruct the command line to use longer lines with:

set lin <number-of-chars>

Use a number that works for your terminal, like 140. This makes the output much more readable.

Also, you may notice that some characters may be replaced with question marks. This is because CLPPLUS has limited support for non-ASCII encodings -- those names contain European characters. This does not affect other applications.

We are now connected to DB2 through Gallium Data, not directly -- this allows us to do all kinds of interesting things.

Changing a query

Let's say we don't want our users to see all customers, but we can't change the query because it's run by an application that cannot be modified.

This is easily done by using a filter in Gallium Data that rewrites the query on its way to DB2.

⇨ Connect to Gallium Data at: http://127.0.0.1:8089

⇨ Log in

⇨ Open the project named Simple Demo - DB2

⇨ Expand the Request filters area

⇨ Open the filter named Hide some customers

Note that the parameters are set so that it executes whenever a specific query is run agains the gallium_demo.customers table.

⇨ Select the Code tab

The code simply rewrites the query to only show some customers.

⇨ Select the Active checkbox

⇨ Click Publish (top)

⇨ Go back to the DB2 command line

This time, the result will be:

         ID FIRST_NAME            LAST_NAME                        COUNTRY

----------- --------------------- -------------------------------- -------

          1 Andrea                Albinioni                        AR     

          5 Eric                  Edmunds                          ES     

         13 Marianne              Mohamed                          MX     

         16 Patricia              P?rez                            PE     

We are only getting the customers that are in the few countries specified in the filter code. The filter in Gallium Data has changed the SQL query, on its way from the SQL command line to the DB2 server, from:

select * from gallium_demo.customers

to:

select * from gallium_demo.customers where country in ('AR','MX','PE', 'ES')

and that's what DB2 has actually executed. 

Rejecting a query

Sometimes, rather than modifying an incoming SQL request, we may wish to simply reject it, and return an error message to the client.

This is easily done with a query filter.

⇨ Go back to the Gallium Data admin app
⇨ Go back to the project view (top nav bar - Project Simple Demo - DB2)
⇨  Expand the Request filters area
⇨ Click on the request filter called Reject certain queries
⇨ Select the Parameters tab

This filter has some parameters that activate it for queries on the table gallium_demo.customers that specifically include the column last_name.

⇨ Select the Code tab

The code rejects the query with an error message.

⇨ Select the Active checkbox

⇨ Click Publish (top)

⇨ Go back to the DB2 command line

The query will fail with an error message:

SQL> select id, last_name from gallium_demo.customers;

ERROR near line 1:

SQL0438N  Application raised error or warning with diagnostic text: "Gallium Data logic has rejected this request".

You can change the error message in the filter code and try again if you're curious -- don't forget to Publish every time you make a change.

Changing a result set

Now let's see how we can modify the data coming back from DB2 before it gets to the client.

Our requirement is that the price of products with a status of 'discontinued' should be set to zero.

First, let's see the data as it is in the database.

You will see a list of products:

         ID NAME                    LIST_PRICE CATEGORY               STATUS              

----------- ----------------------- ------------ -------------------- --------------------

          1 Art Deco lamp                  99.99 Furniture                                

          2 Ballroom gown                 549.99 Clothing             discontinued        

          3 Callipers                       4.50 Tools                                    

          4 Drapes                         46.15 Furniture            back-ordered        

          5 Elixir of youth              2499.99 Pharmacy                                 

          6 Fog horn                       33.33 Tools                                    

          7 Giraffe toy                     7.05 Toys                                     

          8 Hieroglyphics poster           22.99 Furniture            discontinued        

          etc...

Now let's change the data as it finds its way from DB2 to the client. 

We are going to to zero out the price of discontinued products.

⇨ Go back to the Gallium Data admin app
⇨ Go back to the project view (top nav bar - Project Simple Demo - DB2)
⇨  Expand the Response filters area
⇨ Click on the response filter called Hide discontinued product prices
⇨ Select the Parameters tab

This filter has some parameters that activate it for result sets that include data from the gallium_demo.products table.

There is also a parameter specifying that the filter applies to any row with status=discontinued.

⇨ Select the Code tab

The code is a simple one-liner that sets the price to zero.

⇨ Select the Active checkbox

⇨ Click Publish (top)

⇨ Go back to the Cassandra command line

⇨ Re-run the same query (use up-arrow)

This time, all discontinued products have a price of zero:

         ID NAME                    LIST_PRICE CATEGORY               STATUS              

----------- ----------------------- ------------ -------------------- --------------------

          1 Art Deco lamp                  99.99 Furniture                                

          2 Ballroom gown                   0.00 Clothing             discontinued        

          3 Callipers                       4.50 Tools                                    

          4 Drapes                         46.15 Furniture            back-ordered        

          5 Elixir of youth              2499.99 Pharmacy                                 

          6 Fog horn                       33.33 Tools                                    

          7 Giraffe toy                     7.05 Toys                                     

          8 Hieroglyphics poster            0.00 Furniture            discontinued        

          etc...

Nothing has changed in the database -- we're only transforming the data as it goes from DB2 to the database client.

These are simple examples -- we could get very fancy here. We could change data depending on any number of factors, we could generate random data, we could hide certain rows and/or columns, we could even insert rows into the result set. We have complete control, and we can make the database behave in ways that would normally be almost impossible, all without any changes to the database or the database client.

A few things to try at this point, if you feel like it:

If you activate the response filter named Hide furniture and run a query against the products table, you'll see that all products with CATEGORY=Furniture have been removed from the result set.  This is an example of a filter without code: it does what it needs simply by being configured properly.

Again, no changes are made to the database, only to the result set received by the database client.

If you activate the filter Change password, you can log into DB2 with the wrong password! This is because DB2 (when using the plain user ID/password authentication) actually includes the password in the login process, and we can intercept it and change that packet. This will of course not work with more robust authentication mechanisms.

What have we seen?

In this tutorial, you got a glimpse of how Gallium Data can intercept the traffic between database client and database server, and modify this traffic. This enables you to:

• change the behavior of existing applications and databases without changing either the application or the database

• catch unwanted or inefficient requests and change or reject them

• tailor database responses to your exact needs, with a level of precision that would be almost impossible otherwise

• monitor all traffic to and from the databases and react to whatever events are relevant to you

• have a control point in front of your databases, which can run customized logic for your specific needs

Gallium Data has a number of pre-defined filters, but it also makes it easy to create your own filters and be as sophisticated as you want.

Now, the question is: how will you use it?

What to do next

We encourage you to take Gallium Data for a spin with your own database(s). It's always more interesting to work with your own data than with demo data. See the documentation for connection settings for details on setting up a connection to your database.

The tutorial project contains several other filters, but they are not active. You can take a look at them and try to activate them:

• The Change password request filter shows how to modify incoming requests, in this case by changing the user's login password

• Request and response logging filters show requests and/or responses -- see the output in the Logs page of Gallium Data, or with:

docker logs -f gallium-data

Gallium Data is free for end users, so you can use it as much as you want, on your machines, servers, in the cloud, wherever, as long as you do not redistribute it (you'll need a license for that).

Consult the documentation for all the gritty details, such as how to use the debugger, the different filters available for DB2, or the API for various types of DB2 packets.

Footnotes

(1) - Gallium Data is just a container, so it runs in anything that can run a container: Docker, podman, Kubernetes, containerd, etc... You can run this tutorial using something other than Docker, but you'll have to translate the command line options. On recent versions of Windows, you can run Docker in WSL if you prefer.