This works on my DB2 (version 9.7 on Linux/Unix/Windows) by using this syntax: SELECT * FROM PLAYERS WHERE (First_Id, Second_Id) IN (VALUES (1,1), (1,2), (1,3)) This syntax won’t work on DB2 on the Mainframe (at least in version 9.1) because you can’t substitute a sub-select with a VALUES expression. This syntax will work: SELECT * … Read more
Try this: CREATE TABLE SCHEMA.NEW_TB LIKE SCHEMA.OLD_TB; INSERT INTO SCHEMA.NEW_TB (SELECT * FROM SCHEMA.OLD_TB); Options that are not copied include: Check constraints Column default values Column comments Foreign keys Logged and compact option on BLOB columns Distinct types
The documentation is difficult to find, and once you find it, it’s pretty abysmal. Here’s what I’ve found over the past 3 hours. You need to install ibm_db using pip, as follows: pip install ibm_db You’ll want to create a connection object. The documentation is here. Here’s what I wrote: from ibm_db import connect # … Read more
You’re looking for is called an IDENTITY column: create table student ( sid integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) ,sname varchar(30) ,PRIMARY KEY (sid) ); A sequence is another option for doing this, but you need to determine which one is proper for your particular situation. Read this … Read more
Yes, DB2 has the MERGE statement, which will do an UPSERT (update or insert). MERGE INTO target_table USING source_table ON match-condition {WHEN [NOT] MATCHED THEN [UPDATE SET …|DELETE|INSERT VALUES ….|SIGNAL …]} [ELSE IGNORE] See: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0010873.html https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en
LIST=”some string with a substring you want to match” SOURCE=”substring” if echo “$LIST” | grep -q “$SOURCE”; then echo “matched”; else echo “no match”; fi
–for DB2/z select * from sysibm.systables where owner=”SCHEMA” and name like ‘%CUR%’ and type=”T”; –for DB2/LUW select * from sysibm.systables where CREATOR = ‘SCHEMA’ and name like ‘%CUR%’ and type=”T”; This will give you all the tables with CUR in them in the SCHEMA schema. See here for more details on the SYSIBM.SYSTABLES table. If … Read more
You just can put your query as a subquery: SELECT avg(count) FROM ( SELECT COUNT (*) AS Count FROM Table T WHERE T.Update_time = (SELECT MAX (B.Update_time ) FROM Table B WHERE (B.Id = T.Id)) GROUP BY T.Grouping ) as counts Edit: I think this should be the same: SELECT count(*) / count(distinct T.Grouping) FROM … Read more
I got the answer from the sysibm.syscolumns Select distinct(name), ColType, Length from Sysibm.syscolumns where tbname=”employee”;
You can try the following query: SELECT service_level, fixpack_num FROM TABLE (sysproc.env_get_inst_info()) as INSTANCEINFO It works on LUW, so I can’t guarantee that it’ll work on z/OS, but it’s worth a shot.