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"
...
- 30.6k
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 ...
- 98.6k
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
...
- 23.2k
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, ...
- 2,075
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'...
- 5,926
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 ...
- 26.9k
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 ...
- 39.5k
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 ...
- 2,075
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 ...
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 ...
- 23.6k
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) ...
- 26.9k
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 ...
- 16.5k
5
votes
How can I create rows without an underlying table in IBM DB2 SQL?
Platform and version are important when asking about Db2...
In this case it appears you're running Db2 for IBM i (iSeries, AS/400)
What you are looking for is known in the SQL standards as table ...
- 519
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 ...
- 26.9k
5
votes
Accepted
What does 0 mean next to the db2sysc process?
The number 0 after db2sysc represents the database partition number (or instance member number) in a Db2 multi-partitioned environment. In this case, 0 indicates that the instance is running on ...
- 144
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
...
- 51
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 ...
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 ...
- 98.6k
4
votes
SQL0204N "DB2ADMIN.TA" is an undefined name. SQLSTATE=42704
DB2 (as other standard-compliant SQL databases) converts unquoted identifiers to uppercase, so when you say select * from db2admin.ta DB2 will look for the table named 'TA' in the schema 'DB2ADMIN'. ...
- 26.9k
4
votes
Use multiple OUTER JOINS in a CASE statement
The code that produces the syntax error:
SELECT V.I_VIN_FIRST_9, -- many more columns
case when (D.C_ZONE is null or D.C_ZONE = '')
then
(coalesce(DL.C_ZONE, ' ') as ...
- 98.6k
4
votes
Accepted
Retrieving auto-generated keys for an INSERT statement
There are several ways of doing this in Db2 for LUW.
Firstly, there is a function IDENTITY_VAL_LOCAL() that returns one most recently assigned identity value; it can be used when inserting single ...
- 26.9k
4
votes
Accepted
New bufferpool not being used
db2stop/db2start is not required to make a newly created bufferpool useable, but in most cases you must deactivate and reactivate the database for the tablespaces to be able to use the new bufferpool.
...
- 26.9k
4
votes
Accepted
Get tablespace of a given table in DB2
On Db2 for LUW you can use this query. You get one row per table data partition (1 row for a non range partitioned table).
SELECT
SD.TBSPACE AS DATA_SPACE
, SL.TBSPACE AS LONG_SPACE
, SI....
- 778
4
votes
Get current table name trigger was fired on
Is there any way I can obtain inside a trigger the name of the table on which the trigger is defined?
No, there is no such capability in any of the current (as of year 2020) Db2 versions, for reasons ...
- 26.9k
4
votes
Accepted
Display line number where error occurred in DB2 stored procedure
If you are on a currently supported version of Db2 for LUW (11.1 or 11.5) you can use either DBMS_UTILITY.FORMAT_CALL_STACK() or DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() in your signal handler to log ...
- 26.9k
4
votes
Accepted
Extract from DB2 to SQL Server - where is the data in between the two servers?
It depends where the SSIS Package is running, but yes the machine or server it's running on is a passthrough between your DB2 server and your SQL server. So the data is loaded into memory of the ...
- 39.5k
4
votes
Accepted
Column data masking security problem
This is Working As Designed™:
The application of enabled column masks does not interfere with the operations of other clauses within the statement such as the WHERE, GROUP BY, HAVING, SELECT DISTINCT,...
- 26.9k
4
votes
Accepted
Isolation level at the database level for IBM DB2 LUW
In Db2 the isolation level is specified at the statement or package (=a precompiled group of statements) level, not at the database or instance level. If not explicitly specified as part of a ...
- 26.9k
3
votes
Solving Performance issues on Multiple Joins DB2
Assuming that (Part_NO,Supplier,C_Purchase,C_QLTY) is unique a common technique is to pre-aggregate the data like this:
WITH cte AS
( SELECT Part_NO, Supplier, C_Purchase,
MAX(CASE WHEN ...
- 4,196
3
votes
Only top scored, non community-wiki answers of a minimum length are eligible