title: DB2 naming a sequence on create table
body: <p>This may seem like a pointless question, but I'll ask it.</p>
  
  <p>In creating a DDL, I want to have all my relationships and "extra" information NAMED, so when the db throws an error its using MY name and not some auto-generated ID </p>
  
  <p>this is great for my relations and indexes, however I've noticed that DB2 stores "sequences", and it just names them... I can't seem to figure out how to name them in the create script - and going in AFTER creation to figure out what is what is not an option.</p>
  
  <p>EXAMPLE </p>
  
  <pre><code>SEQUENCE MY_SCHEMA.SQL131021121240860 is what DB2 named it when I created
  </code></pre>
  
  <p>What I'd like to do</p>
  
  <pre><code>CREATE SEQUENCE MY_SCHEMA.MY_TABLE_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 32767 0 0 CACHE 20;
  </code></pre>
  
  <p>I've tried this BEFORE the CREATE TABLE, and AFTER... but the sequences ALWAYS go in with this 'SQL131021121240..' naming</p>
  
  <p>Here are the two ways I've tried it....</p>
  
  <pre><code>-- BEFORE, as my ERD software does...
  CREATE SEQUENCE MY_SCHEMA.TEST_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 32767 0 0 CACHE 20;
  
  CREATE TABLE MY_SCHEMA.TEST (
    ID SMALLINT GENERATED BY DEFAULT AS IDENTITY ( START WITH 1, INCREMENT BY 1 ) NOT NULL,
    NAME VARCHAR( 255 ) NOT NULL,
  
    CONSTRAINT MY_SCHEMA_TEST_PK PRIMARY KEY ( ID )
  ) IN STORE16K;
  
  
  -- AFTER, as it makes sense to me, that the table has to exist first
  CREATE TABLE MY_SCHEMA.TEST2 (
    ID SMALLINT GENERATED BY DEFAULT AS IDENTITY ( START WITH 1, INCREMENT BY 1 ) NOT NULL,
    NAME VARCHAR( 255 ) NOT NULL,
  
    CONSTRAINT MY_SCHEMA_TEST2_PK PRIMARY KEY ( ID )
  ) IN STORE16K;
  CREATE SEQUENCE MY_SCHEMA.TEST2_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1000 MAXVALUE 32767 0 0 CACHE 20;
  </code></pre>
  
  <p>In both cases a sequence that is named by the DB shows up in the SEQUENCES folder....</p>
  
  <p>can it be done? is there a reason NOT to do it?, I just hate seeing labels like "SQL131021121240860" showing up in diagrams and DDLs.... </p>
  
  <p>In doing some more reading, it appears as though I don't understand SEQUENCES, however I think my question is still relevant. I'd still like to NAME them. </p>
  
  <p>I'm confused by the fact that sequences get automatically created on identity fields - the comments in this post ( <a href="https://stackoverflow.com/questions/1026936/db2-auto-generated-column-generated-always-pros-and-cons-over-sequence">DB2 Auto generated Column / GENERATED ALWAYS pros and cons over sequence</a> ) make it seem as thou it's an either/or proposition to use 'generated identity' or 'sequences' - but the behavior of CREATE TABLE with a single column  as identity DOES create a sequence...NOT ONLY that but it appears as though it's a 1:1 - which may be needed for identity, and its most likely that a defined sequence can be used by other things.. I get that they are "not bound" to tables specifically....</p>
  
  <p>either way - my question of HOW to NAME them, instead of letting the DB name this is still my point.</p>
  
  <p>Thx</p>