Hot answers tagged

22

I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.) The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water ...


19

No, your colleague is wrong. All SQL proroducts - DBMS that behave according to the SQL standards - provide no guarantee that the result of a query output will be ordered in any way, unless there is an ORDER BY clause in the query. As the IBM DB2 docs mention: Ordering is performed in accordance with the comparison rules described in Language elements. ...


12

Since this old question has been dug up anyway, I'll mention that you can use the built-in XQuery support in DB2 for regular expression matching, something along the lines of select whatever from users where xmlcast( xmlquery('fn:matches($USER_NAME,"^a[aofdmep][a-z][a-z0-9]{4}[sidbfkfpo]")') as integer) = 1 XMLQUERY above calls the XQuery ...


11

By default the global temporary tables are created with the option ON COMMIT DELETE ROWS. Whatever tool you are using to run your statements must have the autocommit option turned on, so as soon as you issue the INSERT statement it is committed, thus deleting rows in the table. You should either create the table using the ON COMMIT PRESERVE ROWS option, or ...


11

As was pointed out in ypercube's answer, when there is no ORDER BY clause there is no defined order. What I would like to add is that it's important to realise that SQL is very much an abstraction, it does not specify step by step what the DBMS is to do but rather specifies your requirements of the end result. This implies that if the data is already ...


10

I don't know about iSeries7 but some DBMS (like Oracle) allow this - as long as t2 (b) is a unique or primary key: UPDATE ( SELECT t1.a AS t1a, t2.a AS t2a FROM t1 INNER JOIN t2 ON t2.b = t1.b ) u SET t1a = t2a ; Tested at (Oracle 11g) SQL-Fiddle Other DBMS allow this syntax (like SQL-Server which doesn't make any fuss about the ...


9

The table MY_TBL contains large binary data in a BLOB column. The documentation of the REORG command says that DB2 avoids reorganizing such objects because it is time consuming and does not improve clustering. However, DB2 can be forced to reorganize LOB data if the LONGLOBDATA option is specified. The unused space can be reused by DB2, so inserting new data ...


9

Unfortunately, it's not possible to take an online backup of a DB2 database if it's in circular logging mode, which is the default for DB2 databases when they are created. You can check whether your database is using circular logging by issuing: ./db2 get db cfg for dbemp | grep LOGARCH If both LOGARCHMETH options are switched off... First log archive ...


9

If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess. db2 grant dataaccess on database to user winuser1 If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be ...


9

You need to select from something. Another option is: db2 => values next value for schema_name.sequence_name; As you noted you can also use SYSIBM.SYSDUMMY1 or create a virtual table: db2 => select next value for schema_name.sequence_name from ( values 1 );


9

What you are trying to do looks very much like a pivot operation. You could probably achieve the result you are looking for without a derived table, using conditional aggregation: SELECT sts_supplier_number, COUNT(CASE WHEN orl_conditie_code = '001' THEN orl_conditie_code END) AS beschadigd, COUNT(CASE WHEN orl_conditie_code = '002' THEN ...


9

There's no restriction on the specific name, other than it must be 8 characters or less. So BACKUP works just fine as a database name: $ db2level DB21085I This instance or install (instance name, where applicable: "db2inst1") uses "64" bits and DB2 code release "SQL10055" with level identifier "0606010E". Informational tokens are "DB2 v10.5.0.5", "...


8

An UPDATE without WHERE clause will update all the rows of the table. For those rows that don't have a matching address_id in table B, the subquery returns an empty result set, so the value is updated to NULL. There's probably a more elegant way to do it but this should only update the matching rows: update TableA A set email = (select email from TableB B ...


7

The files in /opt/ibm/db2/V10.5/ are not to be used for a specific instance. When you run the db2icrt command, it copies a series of files and directories into ~db2inst1/sqllib, and sets the permissions accordingly. (Some of the directories in ~db2inst1/sqllib will be soft links back to directories in /opt/ibm/db2/V10.5). For example, you should find ...


7

Try using the MERGE statement from SQL2003 Standard - which is the equivalent of UPDATE..SET..FROM syntax. Example: MERGE INTO dbo.t1 AS Target USING (SELECT a,b,c FROM dbo.t2) AS Source ON (Target.b = Source.b) WHEN MATCHED THEN UPDATE SET Target.a = Source.a Note: for iSeries, MERGE requires INTO (credit: Jeff Stevens)


6

I've tested two tools that can provide info about transactions including when, who, using which computer and application ApexSQL Audit creates auditing triggers for you, so if you're not up to coding and creating triggers for each of your tables, this is an option It has 2 built in reports, and the good thing about it is that it saves all captured ...


6

DB2 Express-C version 10 and later no longer include a full suite of administration tools as part of the installer (the "Administration Tools" you installed might only include the ones you're looking at...). Like MySQL and SQL Server, the administration tools are now available as a separate download. The official tool you're looking for is IBM Data Studio, ...


6

Add WITH REPLACE to the end of your DECLARE GLOBAL TEMPORARY TABLE statement. This will create your table in QTEMP regardless of whether one already exists or not.


6

In DB2, a tablespace is the object that serves as a layer of abstraction between tables (and indexes) and disk. It is similar to a file group in MS SQL Server. In DB2, a database is roughly equivalent to a SQL Server instance – a database contains multiple tablespaces, transaction logs, one or more bufferpools and other memory areas. A "database" in ...


6

This is a typical "greatest N per group" problem which is usually solved using window functions: select caseno, date, time, tranno, pcode, pdesc, user from ( select a.caseno,a.date,a.time,a.tranno,b.pcode,c.pdesc,a.user, row_number() over (partition by a.caseno order by a.tranno desc) as rn from tablea a right join tableb b on a.caseno=b....


6

This is completely normal. Each of these directories are for each unique log chain. If you are familiar with software version control, each log chain is like a branch. A new log chain is created each time you restore a database and roll forward to a point in time other than end of logs. Here is why: You have a database, SAMPLE. It has reached the log ...


6

Sum after you did the union (hope DB2 does it this way): SELECT BRAND, BRAND_GROUP, SUB_BRAND, SUM(INCOME) AS TOTAL_INCOME FROM (SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME FROM "tema".MMT WHERE BRAND_GROUP IS NULL AND SUB_BRAND IS NULL UNION ALL SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME ...


6

You would need to use the -td<statement terminator> syntax. So you would need to format the above as follows: db2 -td/ -svf db2.sql This would tell DB2 that your statement terminator is the / character, otherwise, yes the default is the semicolon ;.


6

You cannot use the CASE expression in DB2 this way. The result of a CASE expression cannot be a boolean value. Your WHERE clause might look something like this, if one were to blindly translate your code: WHERE COUNTRY_CD = '81930' AND LANG_CD = '02' AND ( ( PARM_ADTR_ID = 'ALL' AND (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%') ) ...


6

There are no users in DB2 that are not OS users. The client software (not only Toad is guilty of this) makes this a bit confusing: what they show as "users" are actually grants given to the (externally defined) users. As you have discovered, you can grant privileges to arbitrary users or groups, and no verification is done if those users and groups exist at ...


6

This simple question has caused an unusual amount of debate, and I feel compelled to quote an authoritative source. The database stores all data in tables that are related to one another. Relationships are established between tables such that data is shared and duplication is minimized. A table space is a storage structure containing tables, indexes, large ...


6

Yes. Tablespaces in DB2 have a MAXSIZE attribute that you can use. You can specify these size attributes in the CREATE DATABASE statement (or when creating additional tablespaces), or use ALTER TABLESPACE to set them later. create database mydb automatic storage yes on C:\mydb user tablespace managed by automatic storage initialsize 20 M ...


6

Use NOT EXISTS: Declare @T table (id int, [type] varchar(5)) insert into @t(id,[type]) values(1,'B'),(2,'A'),(2,'B'),(3,'A') SELECT * FROM @T TAB1 WHERE [TYPE] <> 'A' UNION ALL SELECT * FROM @t TAB1 WHERE [type] = 'A' AND NOT EXISTS ( SELECT * FROM @t WHERE id = TAB1.id AND [type] = 'B' ) The above ...


6

You cannot use a subselect in a check constraint; you can only reference columns from the same table. However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for. ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id) Presumably you have already defined a primary key ...


5

To be more precise, NOT FENCED routines run in the same process space as the database manager itself. The engine is written in C, so calling a not fenced routine is just like calling another C function from main(). This is where all the memory corruption and performance aspects come from: a not fenced routine has access to all the same resources -- memory, ...


Only top voted, non community-wiki answers of a minimum length are eligible