You can get all the column names and the entire data from your table using the code below. writeToFile method will contain the logic to writing to file (if that was not obvious enough 🙂 ) ResultSetMetaData metadata = rs.getMetaData(); int columnCount = metadata.getColumnCount(); for (int i = 1; i <= columnCount; i++) { writeToFile(metadata.getColumnName(i) … Read more
LISTAGG function is new function in DB2 LUW 9.7 see example: create table myTable (id int, category int); insert into myTable values (1, 1); insert into myTable values (2, 2); insert into myTable values (5, 1); insert into myTable values (3, 1); insert into myTable values (4, 2); example: select without any order in grouped … Read more
To add to James’ answer and save people time looking around, you could execute CALL SYSPROC.ADMIN_CMD(‘REORG TABLE MY_TABLE_NAME’) via any available SQL client (i.e. even over ODBC or JDBC connection) to rectify this problem. However, the connection has to be in autocommit mode and you have to have admin privileges to execute this command. I … Read more
Could you do a union in the query? SELECT -1 AS ID , ‘ALL THINGS’ AS NAMEOFTHING FROM DUAL /*’FROM DUAL’ is an Oracle thing, not sure if you need to do something like that in DB2*/ UNION SELECT ID, NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING Apparently, this is how it should be done for … Read more
You can find the codes in the DB2 Information Center. Here’s a definition of the -302 from the z/OS Information Center: THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE On Linux/Unix/Windows DB2, you’ll look under SQL Messages to find your error … Read more
I assume this forum posting, which I quote fully below, should answer the question. Inside a procedure, function, or trigger definition, or in a dynamic SQL statement (embedded in a host program): BEGIN ATOMIC DECLARE example VARCHAR(15) ; SET example=”welcome” ; SELECT * FROM tablename WHERE column1 = example ; END or (in any environment): … Read more
DB2 is strongly typed, so you need to tell DB2 what kind of column your NULL is: select … cast(NULL as int) as column_A, cast(NULL as varchar(128)) as column_B, … FROM …
Use two apostrophes ” to get a single apostrophe on DB2 too, according to the DB2 Survival Guide. Isn’t that working for you?
I’m assuming you’re using DB2 for z/OS, which unfortunately (for whatever reason, I never really understood why) doesn’t support using a values-list where a full-select would be appropriate. You can use a select like below. It’s a little unwieldy, but it works: INSERT INTO tableName (col1, col2, col3, col4, col5) SELECT val1, val2, val3, val4, … Read more