DB2 - SQL - First few SQLS

A) Record to get data in a range of row numbers


SELECT * FROM
(SELECT name, rownumber() over
(order by name)
AS rn FROM address)
AS tr WHERE rn between 10 and 20

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

B) Using SELECT statement to UPDATE records in SQL

You can use SELECT statement to update records through UPDATE statement.
In the SET Clause use the SELECT statement along with desired filters to
update the records.

UPDATE tblDestination
SET tblDestination.col=value
WHERE EXISTS (
SELECT col2.value
FROM tblSource
WHERE tblSource.join_col=tblDestination.join_col
AND tblSource.constraint=value)

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

C) Find code page/ encoding of tables


SELECT ENCODING_SCHEME
FROM SYSIBM.SYSTABLES
WHERE NAME = 'IMPADMIN.ATV'

What to do with Black Pepper...Indexes

Indexes:
A table has got unique index of which all participating column being foreign keys.
This table has got one more index which also has those foreign keys columns along with other columns of index.

It is perfectly normal as per TLMR.

not in & NOT EXISTS... SUGAR and SUGAR CANE

NOT EXISTS can yield different results from NOT IN.
---------------------------------------------------------------
NOT IN implies that data exists in the column. A predicate testing a
column containing a NULL value will not satisfy the NOT IN condition.
(verified on UDB LUW 8.1)

A null value in a code will satisfy the NOT EXISTS clause in your second
example. This is true even if t2 has a row containing a null value in eid!

Ussse Ful Spices !!!

Find Out matching Columns from tables

SELECT TBNAME FROM SYSIBM.SYSCOLUMNS WHERE NAME = 'val'

Tip:

Adding the schema name/creator name in the select list will be helpful

If you are using DB2 on Unix/Win as a standard use syscat.columns ...

First Dish in the making....Data base creation

CREATE DATABASE Command - Very Useful recipe..may be eat only when doing Party.. ;)

999 INGREDIENTS
* Authorization
You must have one of the following:
* 9 sysadm
* 9 sysctrl

* Required connection

999 Prepared Ness

This command is not valid on a client.

Instance. 9 To create a database at another (remote) node, you must first attach to that node. 9 A d


The CREATE DATABASE command initializes a new database with an optional user-defined collating sequence,
creates the three initial table spaces,
creates the system tables,
and allocates the recovery log file.
-> When you initialize a new database, you can specify the AUTOCONFIGURE option to <> display and optionally apply the initial values for the buffer pool size, <> database and database manager parameters.
The AUTOCONFIGURE option 9 is not available in a partitioned database environment.



Scope

In a partitioned database environment, this command affects all database partitions that are listed in the db2nodes.cfg file.

The database partition from which this command is issued becomes the catalog database partition for the new database.
Authorization

9

9 Instance. 9 To create a database at another (remote) node, you must first attach to that node. 9 A database connection is temporarily established by this command during processing.
Command syntax