43 votes
Accepted

Why is the default page size for databases so small?

I'm running Linux (Fedora 34, 64 bit, two cores, four CPUs, 32 GB RAM - PostgreSQL 13.3). If I run stat -f some_random_file as follows: [pol@fedora inst]$ stat -f blah.txt File: "blah.txt" ...
Vérace's user avatar
  • 29.9k
11 votes

Update using select in DB2

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 ...
ypercubeᵀᴹ's user avatar
9 votes
Accepted

group a multiple union statement

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 ...
Andriy M's user avatar
  • 23k
9 votes
Accepted

Why DB2 cannot allow creation of database with keyword "BACKUP" in its name?

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, ...
Ian Bjorhovde's user avatar
9 votes

Why is the default page size for databases so small?

I'm gonna answer from my experience with SQL Server, although I believe the reason might be the same for the other RDBMS you mentioned. If you check the Pages and Extents Architecture Guide doc, you'...
Ronaldo's user avatar
  • 5,079
8 votes
Accepted

Difference between a schema and a database in db2

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. ...
mustaccio's user avatar
  • 26k
7 votes
Accepted

DB2 add user with a password

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 (...
mustaccio's user avatar
  • 26k
7 votes
Accepted

Select subquery CHECK constraint DB2

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 ...
mustaccio's user avatar
  • 26k
7 votes

Why is the default page size for databases so small?

Small is a subjective term in this context. The bigger the Page Size setting is in a database, the more data that will be stored to a Page, and therefore more data that needs to be loaded any time a ...
J.D.'s user avatar
  • 37.5k
6 votes
Accepted

How to use a CASE expression in the WHERE clause?

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 ...
mustaccio's user avatar
  • 26k
6 votes
Accepted

How to set a filesystem size limit for DB2 database? Is it possible?

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 ...
Ian Bjorhovde's user avatar
6 votes
Accepted

Query A if there is no B

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 ...
Scott Hodgin - Retired's user avatar
5 votes

The database manager failed to allocate shared memory because an operating system kernel memory limit has been reached

I just had the same problem! In my case the problem was just the memory of the DB2 instance. In windows type: db2 get dbm cfg | findstr MEMORY In linux: db2 get dbm cfg | grep MEMORY You will get ...
Lucas Guarnieri's user avatar
5 votes

what is force application? What are its advantages and disadvantages?

This was the first search result for me: FORCE APPLICATION command Forces local or remote users or applications off the system to allow for maintenance on a server. Source: IBM Knowledge Center. You ...
Andriy M's user avatar
  • 23k
5 votes

Selecting rows that might contain null values based on column comparison

You can also try a solution based on window MAX(): WITH FirstMatches AS ( SELECT *, MAX(CASE WHEN DateA = DateB AND DateB BETWEEN '2011-01' AND '2011-12' THEN DateA END) ...
Andriy M's user avatar
  • 23k
5 votes
Accepted

update values based on values from another table

I assume LUW and a recently new one. Probably clearest to do with a MERGE statement like: MERGE INTO TA USING ( SELECT OLDLOGIN, NEWLOGIN FROM TB ) AS TB ON TA.LOGIN = TB.OLDLOGIN WHEN ...
Lennart - Slava Ukraini's user avatar
5 votes
Accepted

Finding rows that are not duplicate

Pretty straightforward: select e.* from EmployeeFile e inner join ( select FNAME, LNAME, DOB from OriginalData group by FNAME, LNAME, DOB having count(1) = 1 ) o on (e.FNAME, e.LNAME, e.DOB) ...
mustaccio's user avatar
  • 26k
5 votes

DB2: Left Join Over-ridden by Where clause?

DB2: Left Join Over-ridden by Where clause? If you add search arguments to the where clause, it is going to filter that out, even if it is a left join. As the where clause is processed after the left ...
Randi Vertongen's user avatar
5 votes
Accepted

In DB2 LUW, When should I use a 4K, 8K, or 16K tablespace rather than just creating a 32K tablespace and being done with it?

It's a good question, but the answer is worthy of a whole chapter in a book that does not exist, sadly. Ember Crooks' article you're linking to is a good overview; I'll add here some random factors ...
mustaccio's user avatar
  • 26k
4 votes
Accepted

DB2 Reorg-recommended commands

A table is placed in the reorg pending mode if you alter its physical structure, such as add or drop a column or change the column data type or nullability. Some operations do not place the table in ...
mustaccio's user avatar
  • 26k
4 votes

Column or global variable ROWNUM not found

Lets see what we have here. Inside the parentheses we have a derived table (often called a subquery although this is a more general term), which is then aliased with the name C: ... ...
ypercubeᵀᴹ's user avatar
4 votes

IBM i Access ODBC driver returning only first letter of column names

I believe this is a bug with the specific version of IBM's driver I was using. I was using the ODBC driver from IBM i Access Client Solutions Linux AP v07.03.00. I replaced that driver with the ODBC ...
Liv Carman's user avatar
4 votes
Accepted

IBM i Access ODBC driver returning only first letter of column names

The defect with only the first character of the column name being returned is described in IBM APAR SE63700: http://www-912.ibm.com/n_dir/nas4apar.NSF/b6c9b771e38b5ea2862564c00079d110/...
Alexander Marquis's user avatar
4 votes
Accepted

DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002

Sounds like you are not properly deallocating objects like Statement, ResultSet, etc., so your application is holding open statement handles in the database. This will eventually exhaust "CLI ...
Ian Bjorhovde's user avatar
4 votes
Accepted

What is the meaning of `(ORDER BY x RANGE BETWEEN n PRECEDING...)` if x is a date?

Firstly, the expression values current_date - 1 would only be valid if Oracle compatibility mode were in effect -- it mimics the Oracle's datetime arithmetic where the default interval is expressed in ...
mustaccio's user avatar
  • 26k
4 votes

How to recover deleted records with minimal downtime?

I believe DB2 Recovery Expert is the best option. In the case of a dropped object, or recovery to a specific point in time, you should use the "Recovery" option. The "undo SQL" option performs Log ...
Maira Teixeira de Melo's user avatar
4 votes

Database engine with counting b-trees for efficient paging support

SQL Databases ... don't do paging well at all. Actually, databases (or RDBMS to be precise) don't do paging at all. That's up to client programs. But there are smart ways to query DBs involving ...
Erwin Brandstetter's user avatar
4 votes

Selecting rows that might contain null values based on column comparison

I assume that by "first" you mean when the rows are ordered by DateA DESC. Then you need to find the maximum date per Idkey with a simple group by query and then join back to the table: with ...
ypercubeᵀᴹ's user avatar
4 votes

How to transform "dot" to "slash"?

to_char(MyDate, 'DD/MM/YYYY') is the correct answer (or to_char(MyDate, 'DD;MM;YYYY') if you want ; instead of /) Beware : MyDate must be a timestamp zone. If MyDate is a Date Zone, you should code ...
user106711's user avatar
4 votes
Accepted

Syntax problem in union query

You want to group by again the result of the union. For that you need to enclose the union in parentheses and add an alias ("name" it), i.e. make it a derived table: select datum, sum(resColA) as ...
ypercubeᵀᴹ's user avatar

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