DB2 Questions on Primary Key, View , Static SQL and Dynamic SQL

(Role of DB2 in the age of Big data)
(Role of DB2 in the age of Big data)

What’s the advantage of using a view?
By using views, you can set up different presentations of the same data. Each view is derived from the actual table data, but each user will see a subset of the data. The main benefit to using views is that they allow you to control the access your users have to restricted data.

Give some reasons why you would want to define a primary key for a table?

Defining a primary key for a table enables you to guarantee that each row in the table is unique. Defining a primary key can also improve performance because table access is quicker when each row is uniquely identified. If you’re using an ODBC application to update your data, you need to have a primary key defined for the table. An index created automatically for the primary key helps DB2 find an efficient path to the data.

What’s an instance? What’s the advantage of using an instance?
An instance is a logical database manager environment. Having an instance enables you to catalog databases and set specific configuration parameters to the environment. For example, you can set up a test environment and a production environment by creating two instances. You can change the catalog information and the configuration parameters in the test instance without affecting the data in the production instance. Using instances can also help you protect access to sensitive information because each instance has a separate assignment of authorized users.

What’s the difference between static and dynamic SQL?
When you write a SQL statement for an application program and know the entire statement, including the SQL statement type (UPDATE, INSERT, and so on) and the table and column names that the statement acts on, you’re using static SQL. Static SQL is a SQL statement that’s fully written in an application program before the program is compiled. Dynamic SQL are statements that the application builds and executes at runtime. Generally dynamic SQL is used in an interactive program in which the user is prompted for key parts of the SQL statement.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.