The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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 ...


18

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. ...


15

Singleton Table. In relational terms it is a relation with primary key ∅ (the empty set).


15

All of the platforms you have mentioned can run close to zero data loss configurations. All of them could be deployed in a configuration that will fail. Platform choice is one part of the puzzle. It will be your implementation of the platform that determines whether or not your requirements are met. MySQL Cluster Oracle RAC PostgreSQL High Availability SQL ...


11

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

SELECT a.WorkCenter , a.ActionDate , a.Hours , r.Rate , r.Rate * a.Hours AS Cost FROM Activities AS a JOIN Rates AS r ON r.StartDate = ( SELECT MAX(StartDate) FROM Rates WHERE StartDate <= a.ActionDate ) ; or a GROUP BY solution: SELECT a.WorkCenter , a.ActionDate , ...


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

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 ...


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 ...


8

you can do a db2look, which will give you the ddls for the table. db2look -d db_name -e -x -z schema_name -t table_name


8

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 ...


8

In my view, DB2 is the clear cut winner. What about PostgreSQL: cores: unlimited mem: unlimited size: unlimited license: PostgreSQL (BSD style) imho, PostgreSQL beats all Express/cripled databases.


8

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", "...


7

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 ...


7

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 ...


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

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....


6

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 ...


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 ;.


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