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 ...
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. ...
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
as integer) = 1
XMLQUERY above calls the XQuery ...
, r.Rate * a.Hours AS Cost
Activities AS a
Rates AS r
ON r.StartDate =
( SELECT MAX(StartDate)
WHERE StartDate <= a.ActionDate
or a GROUP BY solution:
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 ...
I don't know about iSeries7 but some DBMS (like Oracle) allow this - as long as t2 (b) is a unique or primary key:
( SELECT t1.a AS t1a, t2.a AS t2a
INNER JOIN t2 ON t2.b = t1.b
t1a = t2a ;
Tested at (Oracle 11g) SQL-Fiddle
Other DBMS allow this syntax (like SQL-Server which doesn't make any fuss about the ...
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 ...
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 ...
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 ...
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:
COUNT(CASE WHEN orl_conditie_code = '001' THEN orl_conditie_code END) AS beschadigd,
COUNT(CASE WHEN orl_conditie_code = '002' THEN ...
I think you are missing couple important parameters. If you believe the project will advance , you have to keep in mind that one day you will need to switch to non-express version. And at that point license cost becomes very important; switching to another RDMS may require serious refactoring (and usually it does). For startup project I'd not pay too much ...
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 ...
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:
DB21085I This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL10055" with level
Informational tokens are "DB2 v10.5.0.5", "...
First of all, if this is homework, please tag it as such.
Secondly if it's not homework and you're doing this in a professional environment, get a professional to do it (or at least to thoroughly scrutinize your final design). Schema design underpins your application design, and flows on from clarity in business requirements and how well you understand ...
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 ...
Try using the MERGE statement from SQL2003 Standard - which is the equivalent of UPDATE..SET..FROM syntax.
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)
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 );
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 ...
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 ...
Here is the information on the CREATE INDEX statement for DB2. You may also want to check these links on designing indexes and space requirements for indexes as they also deal with your question on what factors into index page space.
Based on the last link it would seem to me that the page size for the index would be your column lengths + the index overhead....
Yes, it is a valid physical design modification to accommodate more flexible locking behavior.
If you join the two tables and expose it to your ORM with a view, I don't think the ORM will know the difference (unless it attempts to look at the view metadata like looking for primary key or something during code generation).
Your view should be updatable, and ...
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, ...
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 ...
This is a typical "greatest N per group" problem which is usually solved using window functions:
select caseno, date, time, tranno, pcode, pdesc, 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....
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 ...
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
WHERE BRAND_GROUP IS NULL
AND SUB_BRAND IS NULL
SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME
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 ;.