Skip to main content

Read Data from Table

The easiest way to retrieve data from a table is to send a GET request with the table name.

Table Access Rules
  • If the database user/role has permission to access the table then it will be considered for query.
  • In case the user has access to multiple schema, the table name must be unique if DB2Rest is not running in multi-tenant mode.
DB Name

Database Name configuration is set as pgdb in the example below. Refer to the configuration documentation to set database name. The database name is always part of the URI.

curl --request GET \
--url http://localhost:8080/v1/rdbms/pgdb/film \
--header 'Accept-Profile: ' \
--header 'User-Agent: insomnia/8.4.5'

DB2Rest will respond with HTTP status code 200 and return a list of results.

HTTP/1.1 200
Content-Type: application/json
Transfer-Encoding: chunked

[
	{
		"film_id": 1,
		"title": "ACADEMY DINOSAUR",
		"description": "A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies",
		"release_year": "2006-01-01",
		"language_id": 1,
		"original_language_id": null,
		"rental_duration": 6,
		"rental_rate": 0.99,
		"length": 86,
		"replacement_cost": 20.99,
		"rating": "PG",
		"special_features": "Deleted Scenes,Behind the Scenes",
		"last_update": "2006-02-15T11:03:42.000+00:00"
	},
	{
		"film_id": 2,
		"title": "ACE GOLDFINGER",
		"description": "A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China",
		"release_year": "2006-01-01",
		"language_id": 1,
		"original_language_id": null,
		"rental_duration": 3,
		"rental_rate": 4.99,
		"length": 48,
		"replacement_cost": 12.99,
		"rating": "G",
		"special_features": "Trailers,Deleted Scenes",
		"last_update": "2006-02-15T11:03:42.000+00:00"
	},
    ....
]

This query will:

  • fetch all the rows from the table.
  • it will include all the columns from the table.

This is not ideal, if the table has many records as it will result in a full table scan and lot of data transported over the network. It may also result in time-outs. DB2Rest can solve these problems by:

  1. Column filtering - Allowing specifying the names of the columns that should be included in the result-set.
  2. Row filtering - Apply filtering criteria on the table rows to retrieves only the matching rows.
  3. Pagination - Retrieve only a subset of the table rows that match the horizontal filtering. For example, if the row filters match 35 rows and requested page size is 10, then this means 4 pages of rows - where first three pages contain 10 rows and the last one has 5 row. In order to browse through these rows, 4 separate requests have to be made.

The next sections will discuss filtering in greater detail.