Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Wed Apr 21, 2004 8:39 am Post subject:
drajasekharreddy,
Please post all new queries as a new post .I moved your query to a new post as it does not belong to the topic you posted. You posted your query in "Getting error -204 : How to set SQLID dynamically." which has no relation to your query.
Now to answer your question I will try to explain it with an example.
Let us you have program that updates a db2 table. You issue a commit after every 500 rows. Nowlet us say you have updated 250 records. But even though you have updated these rows, they are not yet reflected on the table. This can be referred as logical update.
Now once you completed 500 updates you issue a commit , and now all these updates are committed which means that you have physically updated the table.
So in brief, an unit of work between two commits can be referred as logical update/delete/insert. Once you issue the commit they are permanent and you can refer them as physical update/delete/insert.
Yes you can insert/delete/update into a view which is defined on a table without a primary key
but if i insert a row into the view which was created without primary key, praimary key will be not null, so what will be inserted into that cloumn. if i insert thru view even though it will insert into base table.
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
Posted: Thu Apr 22, 2004 5:47 am Post subject:
drajasekharreddy,
Quote:
but if i insert a row into the view which was created without primary key, praimary key will be not null, so what will be inserted into that cloumn. if i insert thru view even though it will insert into base table.
I guess you need to get the DB2 basics right. View does not exist physically. It is on the table in the background that you perform all your insertion/deletion/updation. View is just logical object to show information of few columns of the table for which it was defined.
Also the null values depend on the column definition but has got nothing to do with the primary key. Just because a table is defined without a primary key that does not mean that table cannot contain nulls.
for ex: I have created a table/view as shown below
Code:
CREATE TABLE TABLE1
(FIRST_NM CHAR(30)
,LAST_NM CHAR(30)
,EMP_ID INTEGER)
IN USERTS
;
CREATE VIEW VIEW1(EMP_ID,FIRST_NM)
AS SELECT EMP_ID,FIRST_NM FROM TABLE1
;
Note that the table definition of all the columns allows nulls
Now insert a few rows into the view.
Code:
INSERT INTO VIEW2 VALUES(1,'A');
INSERT INTO VIEW2 VALUES(2,'B');
INSERT INTO VIEW2 VALUES(3,'C');
Now if you select from the table it will contain
Code:
---------+---------+---------+---------+---------+--------
FIRST_NM LAST_NM EMP_ID
---------+---------+---------+---------+---------+--------
A ---------------------- 1
B ---------------------- 2
C ---------------------- 3
See the last_nm contains '-----' which means it contains null
On the other hand if you created your table as follows, then
Code:
CREATE TABLE TABLE1
(FIRST_NM CHAR(30) NOT NULL WITH DEFAULT
,LAST_NM CHAR(30) NOT NULL WITH DEFAULT
,EMP_ID INTEGER) NOT NULL WITH DEFAULT
IN USERTS
;
Now the select will bring the following rows after the insert.
Code:
---------+---------+---------+---------+---------+--------
FIRST_NM LAST_NM EMP_ID
---------+---------+---------+---------+---------+--------
A 1
B 2
C 3
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum