By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

SYSCOLUMNS - COLTYPE or TYPENAME?

P: n/a
I am one of the authors of MyGeneration, I'm working on enhancing our
DB2 support, which datatype do I want to use from SYSCOLUMNS when it
comes to creating parameters to stored procedures or datatypes for a
CREATE TABLE statemment?

COLTYPE TYPENAME
=====================
CHAR CHARACTER
LONGVCHAR LONG VARCHAR
TIMESTMP TIMESTAMP

If I was creating a parameter would I use

char(50) or character(50) ?

Again, Should I use COLTYPE or TYPENAME FROM SYSCOLUMNS if my
intentions are to generate valid DB2 SQL script code?
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Mike wrote:
I am one of the authors of MyGeneration, I'm working on enhancing our
DB2 support, which datatype do I want to use from SYSCOLUMNS when it
comes to creating parameters to stored procedures or datatypes for a
CREATE TABLE statemment?

COLTYPE TYPENAME
=====================
CHAR CHARACTER
LONGVCHAR LONG VARCHAR
TIMESTMP TIMESTAMP

If I was creating a parameter would I use

char(50) or character(50) ?
CHAR is identical to CHARACTER.
Again, Should I use COLTYPE or TYPENAME FROM SYSCOLUMNS if my
intentions are to generate valid DB2 SQL script code?


You shouldn't use SYSCOLUMNS (or any other object from the SYSIBM schema,
except SYSIBM.SYSDUMMY1) because that table is not documented and it might
change or go away at any time. Instead, stick to the documented catalog
views from the SYSCAT schema (see Appendix D in the SQL Reference or here
http://publib.boulder.ibm.com/infoce.../r0011297.htm).

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2

P: n/a
> You shouldn't use SYSCOLUMNS (or any other object from the SYSIBM schema,
except SYSIBM.SYSDUMMY1) because that table is not documented and it might
change or go away at any time. Instead, stick to the documented catalog
views from the SYSCAT schema (see Appendix D in the SQL Reference or here


Okay, thanx, I switched but my question remains, can you define a
parameter like this:

LONG VARCHAR(100) or do you have to use LONGVCHAR(100)

and for "bit data" types would I say:

CHAR FOR BIT DATA(30)
CHARACTER FOR BIT DATA(30)

or none of the above?

- Mike
Nov 12 '05 #3

P: n/a
Mike wrote:
Okay, thanx, I switched but my question remains, can you define a
parameter like this:

LONG VARCHAR(100) or do you have to use LONGVCHAR(100)

and for "bit data" types would I say:

CHAR FOR BIT DATA(30)
CHARACTER FOR BIT DATA(30)


You did install DB2 UDB V8 on one of your machines, right? You could also
install Information Center on one or your machines or visit:

http://publib.boulder.ibm.com/infoce...help/index.jsp

In my personal opinion checking syntax in the newsgroup appears to be excessive
use of internet - not mentioning even fact that your checks with documentation
would be significantly faster.

Nevertheless, here is relevant fragment from the syntax diagram:

+-+-+-CHARACTER-+--+-----------+--------------+--+---------------------+-+
| | '-CHAR------' '-(integer)-' | | (1) | |
| +-+-VARCHAR----------------+--(--integer--)-+ '--------FOR BIT DATA-' |
| | '-+-CHARACTER-+--VARYING-' | |
| | '-CHAR------' | |
| '-LONG VARCHAR------------------------------' |

which should answer your current question.
Jan M. Nelken
Nov 12 '05 #4

P: n/a
Mike wrote:
You shouldn't use SYSCOLUMNS (or any other object from the SYSIBM schema,
except SYSIBM.SYSDUMMY1) because that table is not documented and it
might
change or go away at any time. Instead, stick to the documented catalog
views from the SYSCAT schema (see Appendix D in the SQL Reference or here
Okay, thanx, I switched but my question remains, can you define a
parameter like this:

LONG VARCHAR(100) or do you have to use LONGVCHAR(100)


I don't know if the second variant works (never tried it). It is an
unnecessary abbreviation and it would just make things more difficult to
comprehend. That would be a no-go for me.
and for "bit data" types would I say:

CHAR FOR BIT DATA(30)
CHARACTER FOR BIT DATA(30)


As I said before, CHAR and CHARACTER are identical - just synonyms for the
same data type. So either one would be correct:

CHAR(30) FOR BIT DATA
CHARACTER(30) FOR BIT DATA

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #5

P: n/a
Knut, thank you so much, you've been a big help. We're getting a lot
of interest from DB2 folks, maybe because of IBM's Stinger push and
their DB2.NET managed driver, anyway, next I'm writing a template that
will generate the DB2 stored proc's and then an instance of our
dOOdads .NET architecture. I will go with your suggestions, thanx
again.

Mike Griffin
MyGeneration Software
http://www.mygenerationsoftware.com

PS: I'm on UDB 7 but upgrading to UDB 8 ...
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.