{"id":741,"date":"2015-05-07T21:16:07","date_gmt":"2015-05-08T06:16:07","guid":{"rendered":"http:\/\/blog.box.kr\/?p=741"},"modified":"2015-05-07T21:16:07","modified_gmt":"2015-05-08T06:16:07","slug":"db2-how-to-use-sequence-table","status":"publish","type":"post","link":"https:\/\/blog.box.kr\/?p=741","title":{"rendered":"[DB2] how to use Sequence table"},"content":{"rendered":"

1. describe sequence table
\ndescribe table syscat.sequences;<\/p>\n

2.\u00a0 select sequence information
\nSELECT * FROM syscat.sequences;<\/p>\n

3. create sequence
\nCREATE SEQUENCE<\/span> seq_tab START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24;
\n-> start 1, increment\u00a01 by 1 \u00a0to\u00a0\u00a0no limit
\nCREATE SEQUENCE<\/span> seq_tab START WITH 1 INCREMENT BY 1 maxvalue 10000<\/span> NOCYCLE CACHE 24;
\n-> start 1 , increment by 1\u00a0 to\u00a0 10000, and error on if exceed by max value<\/span><\/span><\/span><\/span><\/p>\n

4. modify sequence
\nALTER SEQUENCE<\/span> seq_tab<\/span> INCREMENT BY 5;
\n\u00a0\u00a0\u00a0 -> modify for increment step by 5
\nALTER SEQUENCE <\/span>seq_tab<\/span> \u00a0maxvalue 100;
\n\u00a0\u00a0\u00a0 -> modify for 100 max limit<\/span><\/span><\/span><\/span><\/span><\/p>\n

5. remove sequence
\nDROP SEQUENCE<\/span> seq_tab<\/span>\u00a0 restrict<\/span><\/p>\n

6. use exsample<\/p>\n

CREATE SEQUENCE seq_tab<\/span>\u00a0 START WITH 1 INCREMENT BY 2 NOMAXVALUE NOCYCLE CACHE 24;<\/span><\/p>\n

create table test
\n(
\nidx INTEGER not null primary key,
\nname varchar(10) not null
\n);<\/span><\/p>\n

insert into test (idx,name) values (NEXTVAL FOR seq_tab<\/span>\u00a0 <\/strong>,’name_1′);
\ninsert into test (idx,name) values (NEXTVAL FOR seq_tab<\/span>\u00a0 <\/strong>,’name_2′);
\ninsert into test (idx,name) values (NEXTVAL FOR seq_tab<\/span>\u00a0 <\/strong>,’name_3′);
\n<\/span>
\nselect * from test;
\n<\/span>DROP TABLE TEST;
\n<\/span><\/p>\n

DROP SEQUENCE seq_tab<\/span>\u00a0 <\/strong>restrict;<\/span><\/blockquote>\n

8. like mysql auto_increment column on DB2<\/p>\n

CREATE TABLE<\/strong> customer_orders_t (
\norder_id\u00a0\u00a0 INT NOT NULL <\/strong>GENERATED ALWAYS AS IDENTITY<\/span> (START WITH<\/span><\/strong> 1 <\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INCREMENT BY<\/strong> 1 <\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MINVALUE<\/strong> 1 <\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>NO MAXVALUE NO CYCLE NO CACHE ORDER<\/span>),<\/strong>
\norder_date DATE NOT NULL,<\/strong>
\ncust_id\u00a0\u00a0\u00a0 INT NOT NULL,<\/strong>
\nproduct_id INT NOT NULL,<\/strong>
\nquantity\u00a0\u00a0 INT NOT NULL,<\/strong>
\nprice\u00a0\u00a0\u00a0\u00a0\u00a0 DECIMAL<\/strong>(10,2) NOT NULL,<\/strong>
\nstatus\u00a0\u00a0\u00a0\u00a0 CHAR<\/strong>(9) NOT NULL,<\/strong>PRIMARY KEY<\/strong> (order_date, order_id))<\/blockquote>\n

Fin.<\/p>\n","protected":false},"excerpt":{"rendered":"

1. describe sequence table describe table syscat.sequences; 2.\u00a0 select sequence information SELECT * FROM syscat.sequences; 3. create sequence CREATE SEQUENCE seq_tab START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24; -> start 1, increment\u00a01 by 1 \u00a0to\u00a0\u00a0no limit CREATE SEQUENCE seq_tab START WITH 1 INCREMENT BY 1 maxvalue 10000 NOCYCLE CACHE 24; -> start 1 , increment by 1\u00a0 to\u00a0 10000, and error on if exceed by max value 4. modify sequence ALTER SEQUENCE seq_tab INCREMENT BY 5; \u00a0\u00a0\u00a0 -> modify for increment step by 5 ALTER SEQUENCE seq_tab \u00a0maxvalue 100; \u00a0\u00a0\u00a0 -> modify for 100 max limit 5. remove sequence DROP SEQUENCE seq_tab\u00a0 restrict 6. use exsample CREATE SEQUENCE seq_tab\u00a0 START WITH 1 INCREMENT BY 2 NOMAXVALUE NOCYCLE CACHE 24; create table test ( idx INTEGER not null primary key, name varchar(10) not null ); insert into test (idx,name) values (NEXTVAL FOR seq_tab\u00a0 ,’name_1′); insert into test (idx,name) values (NEXTVAL FOR seq_tab\u00a0 ,’name_2′); insert into test (idx,name) values (NEXTVAL FOR seq_tab\u00a0 ,’name_3′); select * from test; DROP TABLE TEST; DROP SEQUENCE seq_tab\u00a0 restrict; 8. like mysql auto_increment column on DB2 CREATE TABLE customer_orders_t ( order_id\u00a0\u00a0 INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INCREMENT BY […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"ngg_post_thumbnail":0,"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[12,5],"tags":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5q9Zn-bX","jetpack-related-posts":[{"id":647,"url":"https:\/\/blog.box.kr\/?p=647","url_meta":{"origin":741,"position":0},"title":"MYSQL\uc5d0\uc11c AUTO INCREMENT column\uc758 \uc785\ub825 \uac12 \uac00\uc838\uc624\uae30..","date":"2015-04-05","format":false,"excerpt":"INSERT \uc2e4\ud589 \ud6c4\uc5d0\u00a0SELECT LAST_INSERT_ID() ; \ud558\uba74 \ub41c\ub2e4. \u00a0 \ud574\ub2f9 Session\uc5d0\uc11c\ub9cc \uc694\ud6a8 \ud558\uae30 \ub54c\ubb38\uc5d0 \ub3d9\uc77c \ud14c\uc774\ube14\uc5d0 \uc5ec\ub7ec \uc785\ub825\uc774 \uc788\ub354\ub77c\ub3c4 session\uc774 \uc720\uc9c0\ub418\ub294 \ud55c\uc740 \uc720\uc9c0 \ub41c\ub2e4. \uadf8\ub9ac\uace0 \ub370\uc774\ud130 \uc0ad\uc81c\ud6c4 \ub610\ub294 \ud14c\uc774\ube14 \uc0dd\uc131 \ud6c4 \ud2b9\uc815 \ubc88\ud638 \uc774\ud6c4\ub85c \u00a0\uc77c\ub828 \ubc88\ud638\uac00 \uc0dd\uc131\ub418\uac8c \ud560\ub824\uba74 ALTER TABLE\u00a0table_name\u00a0AUTO_INCREMENT = 100; \u00a0 \uc774\ub807\uac8c \ud558\uba74 \ub41c\ub2e4. \u00a0","rel":"","context":"In "\uae30\uc220\uc790\ub8cc"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":807,"url":"https:\/\/blog.box.kr\/?p=807","url_meta":{"origin":741,"position":1},"title":"[scrap] mysql – dual master replication","date":"2015-05-18","format":false,"excerpt":"http:\/\/simonshin.egloos.com\/2247518 \/etc\/my.cnf \ud30c\uc77c \ub0b4\uc6a9 Below is what needs to be added to the configuration for Server A: server-id = 1 replicate-same-server-id = 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/\uc2ac\ub808\uc774\ube0c \uc11c\ubc84\uc5d0\uc11c \uc0ac\uc6a9\ub428. \uc77c\ubc18\uc801\uc73c\ub85c\ub294 \ub514\ud3f4\ud2b8 \uc124\uc815 \uac12\uc778 0\uc744 \uc0ac\uc6a9\ud558\ub294\ub370, \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/\uc774 \uac12\uc740 \uc21c\ud658 \ub9ac\ud50c\ub9ac\ucf00\uc774\uc158(circular replication)\uc5d0 \uc758\ud55c \ubb34\ud55c \ub8e8\ud504\ub97c \ubc29\uc9c0\ud569\ub2c8\ub2e4. \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/\uc774 \uac12\uc744 1\ub85c \uc124\uc815\ud55c\ub2e4\uba74, \uc2ac\ub808\uc774\ube0c\ub294 \uc790\uc2e0\uc758 \uc11c\ubc84\u2026","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":371,"url":"https:\/\/blog.box.kr\/?p=371","url_meta":{"origin":741,"position":2},"title":"Clojure vs Scala","date":"2014-09-15","format":false,"excerpt":"http:\/\/programming-puzzler.blogspot.kr\/2013\/12\/clojure-vs-scala.html Clojure vs Scala Last week, someone posted a question on the Clojure group asking for a comparison between Clojure and Scala. Since my most popular blog post, by far, is my\u00a0Racket vs Clojure\u00a0post from three years ago, I thought it would be good to post my response here.Ten years\u2026","rel":"","context":"In "\ucc38\uace0\ub97c \uc704\ud55c \uc800\uc7a5\ubb3c"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":345,"url":"https:\/\/blog.box.kr\/?p=345","url_meta":{"origin":741,"position":3},"title":"Alter Table [ \uceec\ub7fc \uc0ac\uc774\uc988, \ub370\uc774\ud130 \ud06c\uae30 \uc870\uc815 ]","date":"2014-08-17","format":false,"excerpt":"[DB2] \ucef4\ub7fc\uc758 \ub370\uc774\ud130 \ud0c0\uc785 \ub610\ub294 \uc0ac\uc774\uc988 \uc870\uc815 ALTERTABLE temp \u00a0 \u00a0\u00a0ALTERCOLUMN col1 SET DATA TYPE VARCHAR(60); [ORACLE] alter table\u00a0\u00a0 table_namemodify\u00a0\u00a0 column_name\u00a0 datatype; \uc774\ub7f0\uc2dd\uc73c\ub85c\ub3c4 \ub41c\ub2e4.. alter table\u00a0\u00a0 table_namemodify\u00a0\u00a0 (\u00a0\u00a0 column1_name\u00a0 column1_datatype,\u00a0\u00a0 column2_name\u00a0 column2_datatype,\u00a0\u00a0 column3_name\u00a0 column3_datatype,\u00a0\u00a0 column4_name\u00a0 column4_datatype\u00a0\u00a0 ); [MYSQL] alter table people modify name VARCHAR(35) ;","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":834,"url":"https:\/\/blog.box.kr\/?p=834","url_meta":{"origin":741,"position":4},"title":"[scrap]awk introduction tutorial 7 awk print examples","date":"2015-05-20","format":false,"excerpt":"http:\/\/www.thegeekstuff.com\/2010\/01\/awk-introduction-tutorial-7-awk-print-examples\/ \u00a0 This is the first article on the new awk tutorial series. We\u2019ll be posting several articles on awk in the upcoming weeks that will cover all features of awk with practical examples. In this article, let us review the fundamental awk working methodology along with 7 practical awk\u2026","rel":"","context":"In "\uae30\uc220"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":519,"url":"https:\/\/blog.box.kr\/?p=519","url_meta":{"origin":741,"position":5},"title":"MYSQL \uc0ac\uc6a9\uc790 \uc0dd\uc131\ubd80\ud130 \uad8c\ud55c \ubd80\uc5ec \uae4c\uc9c0..","date":"2014-12-29","format":false,"excerpt":"1. DATABASE \uc0dd\uc131 mysql> CREATE DATABASE \ub514\ube44\uba85 2. \uc0ac\uc6a9\uc790 \uc0dd\uc131 \ubc0f \uad8c\ud55c \ubd80\uc5ec mysql> \u00a0CREATE USER '\uc0ac\uc6a9\uc790\uba85'@'\uc811\uc18d\ud638\uc2a4\ud2b8(localhost, %, ip )' IDENTIFIED BY '\uc554\ud638'; \u00a0 mysql>\u00a0GRANT ALL PRIVILEGES ON \ub514\ube44\uba85.* TO '\uc0ac\uc6a9\uc790\uba85'@'\uc811\uc18d\ud638\uc2a4\ud2b8(localhost, %, ip )'\u00a0\u00a0WITH GRANT OPTION; \ub610\ub294 \u00a0 mysql> GRANT RELOAD,PROCESS ON \ub514\ube44\uba85.* TO '\uc0ac\uc6a9\uc790\uba85'@'localhost'; \/\/ \uc774\ub807\uac8c \ud544\uc694\ud55c \uad8c\ud55c\ub9cc \ucc0d\uc5b4\uc11c...\u2026","rel":"","context":"In "DB\uad00\ub828"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/741"}],"collection":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=741"}],"version-history":[{"count":0,"href":"https:\/\/blog.box.kr\/index.php?rest_route=\/wp\/v2\/posts\/741\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=741"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=741"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.box.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=741"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}