DB2. PORTAL Blog: Using Nulls in DB2. A null represents missing or unknown information at the column level. If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known. Of course, it could be a combination of these two situations, too. A null is not the same as 0 or blank. Null means no entry has been made for the column and it implies that the value is either unknown or not applicable. Because DB2 supports null you can distinguish between a deliberate entry of 0 (for numerical columns) or a blank (for character columns) and an unknown or inapplicable entry (NULL for both numerical and character columns). Null indicates that the user did not explicitly make an entry or has explicitly entered NULL for the column. For example, a null value in the Price column of the ITEM table in some database does not mean that the item is being given away for free; instead it means that the price is not known or has not yet been set. Nulls sometimes are inappropriately referred to as “null values.” Using the term value to describe a null is inaccurate because a null implies the lack of a value. Therefore, simply use the term null or nulls (without appending the term “value” or “values” to it). A Few Examples. When are nulls useful? Well, defining a column as NULL provides a place holder for data you might not yet know. For example, when a new employee is hired and is inserted into the EMP table, what should the employee termination date column be set to? I don’t know about you, but I wouldn’t want any valid date to be set in that column for my employee record. Instead, null can be used to specify that the termination date is currently unknown. Let’s consider another example. Suppose that we also capture employee’s hair color when they are hired. Consider three potential entity occurrences: a man with black hair, a woman with unknown hair color, and a bald man. The woman with the unknown hair color and the bald man both could be assigned as null, but for different reasons. The woman’s hair color would be null meaning presently unknown; the bald man’s hair color could be null too, in this case meaning not applicable. How could you handle this without using nulls? You would need to create special values for the Hair. Color column that mean “bald” and “unknown.” This is possible for a CHAR column like Hair. Color. But what about a DB2 DATE column? All occurrences of a column assigned as a DATE data type are valid dates. It might not be possible to use a special date value to mean “unknown.” This is where using nulls is most practical. DB2 does not differentiate between nulls that signify unknown data and those that signify inapplicable data. This distinction must be made by the program logic of each application. Keep in mind, though, that using null to indicate “not applicable” can be an indication of improper database design. By properly modeling and normalizing your data structures you can usually eliminate the need to use nulls to indicate that a column is inapplicable for a specific row. For example, consider the following table: CREATE TABLE EMP (EMPNO INTEGER NOT NULL, LAST_NAME CHAR(2. NOT NULL, FIRST_NAME CHAR(1. NOT NULL, STREET_ADDR CHAR(3. NOT NULL WITH DEFAULT, CITY CHAR(1. NOT NULL WITH DEFAULT, STATE CHAR(2) NOT NULL WITH DEFAULT, POSTAL_CODE CHAR(1. 3 The DB2 Driver. The following. because of a wildcard or null value, for. For each column in a result set that maps to a column in a table in the database, the. Updating a single column to a NULL value with parenthesis surrounding NULL fails as follows. db2 "update ben set (col2)= (NULL) where col2='one'" DB21034E The. · SELECT NULL VALUES AS ZERO. DB2 Database Forums on Bytes. NOT NULL WITH DEFAULT, EMP_TYPE CHAR(1) NOT NULL CHECK(EMP_TYPE IN 'F', 'C', 'P'), HIRE_DATE DATE, SALARY DECIMAL(9,2), BILLING_RATE DECIMAL(5,2)); In this case, we have a code in the EMP_TYPE column that can contain F (full- time), C (contractor), or P (part- time). We also have a SALARY column that is populated for full- time and part- time employees, but is set to null for contractors; and a BILLING_RATE column that is populated for contractors but set to null for full- time and part- time employees. Additionally, the HIRE_DATE column is set to null for contractors. Well, here we have three columns that are set to null (or not) based on other values in the table. We can design our way out of this problem by creating a separate table for employees and contractors. If additional columns were needed for full- time employees that did not apply part- time employees we might even split the employee table into two: one for full- time and another for part- time. After doing so, there is no more need to use null for inapplicable data. Indicator Variables. DB2 represents null in a special “hidden” column known as an indicator variable. An indicator variable is defined to DB2 for each column that can accept nulls. The indicator variable is transparent to an end user, but must be provided for when programming in a host language (such as COBOL or PL/I). The null indicator is used by DB2 to track whether its associated column is null or not. A positive value or a value of 0 means the column is not null and any actual value stored in the column is valid. If a CHAR column is truncated on retrieval because the host variable is not large enough, the indicator value will contain the original length of the truncated column. A negative value indicates that the column is set to null. If the value is - 2 then the column was set to null as the result of a data conversion error. Let’s take a moment to clear up a common misunderstanding right here: nulls NEVER save storage space in DB2 for OS/3. OS. Every nullable column requires one additional byte of storage for the null indicator. So, a CHAR(1. 0) column that is nullable will require 1. This is the case regardless of whether the column is set to null or not. DB2 for Linux, Unix, and Windows has a compression option that allows columns set to null to save space. Using this option causes DB2 to eliminate the unused space from a row where columns are set to null. This option is not available on the mainframe, though. Syntax. Every column defined to a DB2 table must be designated as either allowing or disallowing nulls. A column is defined as nullable – meaning it can be set to NULL – in the table creation DDL. Null is the default if nothing is specified after the column name. To prohibit the column from being set to NULL you must explicitly specify NOT NULL after the column name. In the following sample table, COL1 and COL3 can be set to null, but not COL2, COL4, or COL5: CREATE TABLE SAMPLE1 (COL1 INTEGER, COL2 CHAR(1. NOT NULL, COL3 CHAR(5), COL4 DATE NOT NULL WITH DEFAULT, COL5 TIME NOT NULL); In SELECT statements, testing for null is accomplished differently than testing for other “values.” You cannot specify WHERE COL = NULL, because this does not make any sense. Remember, null is a lack of a value, so the column does not equal anything. Instead, you would have to code WHERE COL IS [NOT] NULL. In INSERT statements NULL can be specified in the VALUES clause to indicate that a column is to be set to NULL; but in UPDATE statements you can use the equality predicate (=) to assign a column to NULL. When inserting data, if the user fails to make an entry in a column that allows nulls, DB2 supplies the NULL as a default (unless another default value exists). If an attempt to insert NULL is made against a column defined as NOT NULL, the statement will fail. Guidance. Now that you have a good understanding of the basics of nulls, let’s review some guidelines for their usage. Whenever possible, avoid nulls in columns that must participate in arithmetic logic (for example, DECIMAL money values), and especially when functions will be used. The AVG, COUNT DISTINCT, SUM, MAX, and MIN functions omit column occurrences set to null. The COUNT(*) function, however, does not omit columns set to null because it operates on rows. Thus, AVG is not equal to SUM/COUNT(*) when the average is being computed for a column that can contain nulls. The DB2 Driver. 6/1. The following sections describe how to configure and use the Oracle Type 4 JDBC driver for DB2: DB2 Driver Classes. The driver classes for the Oracle Type 4 JDBC DB2 driver are as follows. XA: weblogic. jdbcx. DB2. Data. Source. Non- XA: weblogic. DB2. Driver. Use these driver classes when configuring a JDBC data source in your Web. Logic Server domain. J2. EE Connector Architecture Resource Adapter Class. The Managed. Connection. Factory class for the Informix resource adapter is: com. Informix. Managed. Connection. Factory. DB2 URLThe connection URL format for the DB2 driver is. IP address or TCP/IP host name of the server to which you are connecting. See Using IP Addresses for details on using IP addresses. Note. Untrusted applets cannot open a socket to a machine other than the originating host. TCP/IP port. property=value specifies connection properties. For a list of connection properties and their valid values, see DB2 Connection Properties. For example: DB2 UDB for Linux, UNIX, and Windows. Database. Name=jdbc; User=test; Password=secret. DB2 UDB for z/OS and i. Series. jdbc: weblogic: db. Location. Name=Sample; User=test; Password=secret. DB2 Connection Properties. Table 3- 1 lists the JDBC connection properties supported by the DB2 driver, and describes each property. You can use these connection properties in a JDBC data source configuration in your Web. Logic Server domain. Note. All connection property names are case- insensitive. For example, Password is the same as password. Required properties are noted as such. The data type listed for each connection property is the Java data type used for the property value in a JDBC data source. To specify a property, use the following form in the JDBC data source configuration: property=value. Table 3- 1 DB2 Connection Properties. Property. Description. Accounting. Info. Accounting information to be stored in the database. This value sets the CURRENT CLIENT_ACCTNG register (DB2 for Linux/UNIX/Windows) or the CLIENT ACCTNG register (DB2 for z/OS and DB2 for i. Series) in the database. This value is for database administration/monitoring purposes. Data Type: String. Valid Values: string where string is the accounting information. The default value is an empty string. Add. To. Create. Table. A string that is appended to the end of all CREATE statements. This field is primarily for users who need to add an "in database" clause. Data Type: String. Valid Values: string where string is the set of characters appended to all CREATE statements. Allow. Implicit. Result. Set. Close. For. XATrue or false. DB2 provides a mechanism that automatically closes a result set when all rows of the result set have been fetched. This mechanism increases application performance by reducing the number of database round trips. The Web. Logic DB2 driver uses this mechanism by default. Note: Problems have been noted when using this mechanism. As a workaround, you should add Allow. Implicit. Result. Set. Close. For. XA=false to the properties in your data source configuration. The default is true. Alternate. IDSets the default DB2 schema used by unqualified SQL identifiers to the specified value. For DB2 for Linux/UNIX/Windows and DB2 for i. Series, this property sets the value in the DB2 CURRENT SCHEMA special register. For DB2 for z/OS, this property sets the value in the DB2 CURRENT SQLID special register. Valid Values: For DB2 for Linux/UNIX/Windows and DB2 for i. Series, a valid DB2 schema name. This value is not validated by the database server. For DB2 for z/OS, this value is validated by the database server. Refer to your IBM documentation for valid values for the CURRENT SQLID register. Alternate. Servers. A list of alternate database servers that is used to failover new or lost connections, depending on the failover method selected. See the Failover. Mode property for information about choosing a failover method. Data type: String. Valid Values: (servername. The server name (servername. Port number (port. If the port is unspecified, the port number of the primary server is used. If the port number of the primary server is unspecified, the default port number of 2. Optional connection properties are Database. Name and Informix. Server. Example: The following URL contains alternate server entries for server. The alternate server entries contain the optional Database. Name property. jdbc: weblogic: db. Database. Name=TEST; User=test; Password=secret; Alternate. Servers=(server. 2: 5. Database. Name=TEST2,server. Database. Name=TEST3)Default: None. Application. Name. The name of the application to be stored in the database. This value sets the CURRENT CLIENT_APPLNAME register (DB2 for Linux/UNIX/Windows) or CLIENT APPLNAME register (DB2 for z/OS and DB2 for i. Series) in the database. For DB2 V9. 1 and higher for Linux/UNIX/Windows, this value also sets the APPL_NAME value of the SYSIBMADM. APPLICATIONS table. These values are used for database administration/monitoring purposes. Valid Values: string where string is the name of the application. Your database may impose character length restrictions on the value. If the value exceeds a restriction, the driver truncates it. Data Type: String. Default is empty string. Authentication. Method. Determines which authentication method the driver uses when establishing a connection. Valid Values: Kerberos, encrypted. UIDPassword, encrypted. Password, clear. Text , or client. If kerberos, the driver uses Kerberos authentication. The driver ignores any user ID or password specified. If encrypted. UIDPassword, the driver uses user ID/password authentication. The driver sends an encrypted user ID and password to the DB2 server for authentication. If a user ID and password are not specified, the driver throws an exception. If this value is set, the driver can also use data encryption (see the Encryption. Method property for details). If encrypted. Password, the driver uses user ID/password authentication. The driver sends a user ID in clear text and an encrypted password to the DB2 server for authentication. If a user ID and password are not specified, the driver throws an exception. If this value is set, the driver can also use data encryption (see the Encryption. Method property for details). If set to clear. Text (the default), the driver uses user ID/password authentication. The driver sends the user ID and password in clear text to the DB2 server for authentication. If a user ID and password are not specified, the driver throws an exception. If this value is set, the driver can also use data encryption (see the Encryption. Method property for details). If client, the driver uses client authentication. The DB2 server relies on the client to authenticate the user and does not provide additional authentication. The driver ignores any user ID or password specified. The User property provides the user ID. The Password property provides the password. If the specified authentication method is not supported by the DB2 server, the connection fails and the driver throws an exception. The default is clear. Text. See Authentication for more information about using authentication with the DB2 driver. Batch. Performance. Workaround. The DB2 driver uses the native DB2 batch mechanism. This property determines whether certain restrictions are enforced to facilitate data conversions. Valid Values: true | false. When set to false, the methods used to set the parameter values of a batch operation performed using a Prepared. Statement must match the database data type of the column the parameter is associated with. This is because DB2 servers do not perform implicit data conversions. When set to true, restrictions are removed; however, parameter sets may not be executed in the order they were specified. The default is false. See Batch Inserts and Updates for more information. Note: For data sources used as a JMS JDBC store that use the Oracle Type 4 JDBC driver for DB2, the Batch. Performance. Workaround property must be set to true. Bulk. Load. Batch.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
September 2018
Categories |