IGNORE NULLS possible fro CREATE INDEX? DB2 UDB v8.1.9 Linux

Bob Stearns

I am creating an index on a column which is 40% NULLS. The process seems
to run forever, though a count of the number of values runs in
milliseconds. This leads to the subject question: is there a way to
ignore those rows with nulls in index creation?

Apr 12 '06 #1

Subscribe Reply

2626

Serge Rielau

Bob Stearns wrote:

I am creating an index on a column which is 40% NULLS. The process seems
to run forever, though a count of the number of values runs in
milliseconds. This leads to the subject question: is there a way to
ignore those rows with nulls in index creation?

No. DB2 indexes NULLs. I presume you mean the CREATE INDEX statement
right? It's not the queries that are slow...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 12 '06 #2

Bob Stearns

Serge Rielau wrote:

Bob Stearns wrote:
I am creating an index on a column which is 40% NULLS. The process
seems to run forever, though a count of the number of values runs in
milliseconds. This leads to the subject question: is there a way to
ignore those rows with nulls in index creation?

No. DB2 indexes NULLs. I presume you mean the CREATE INDEX statement
right? It's not the queries that are slow...

Yes. An IBM DB that I previously used (used to be called Universe, I
can't remember the IBM name, it's IBM's mv offering) had such a clause
for indices; I'd hoped IBM had done an internal IP transfer. It also has
powerful capabilities in the 'GENERATED VIRTUAL' (including referring to
other tables, albeit only by primary key) area that I wish would migrate
into DB2. Some of my tables had more virtual columns than real ones.

Apr 12 '06 #3

Serge Rielau

Bob Stearns wrote:

Serge Rielau wrote:
Bob Stearns wrote:
I am creating an index on a column which is 40% NULLS. The process
seems to run forever, though a count of the number of values runs in
milliseconds. This leads to the subject question: is there a way to
ignore those rows with nulls in index creation?

No. DB2 indexes NULLs. I presume you mean the CREATE INDEX statement
right? It's not the queries that are slow...

Yes. An IBM DB that I previously used (used to be called Universe, I
can't remember the IBM name, it's IBM's mv offering) had such a clause
for indices; I'd hoped IBM had done an internal IP transfer. It also has
powerful capabilities in the 'GENERATED VIRTUAL' (including referring to
other tables, albeit only by primary key) area that I wish would migrate
into DB2. Some of my tables had more virtual columns than real ones.

Universe is still around under this name.
Looking at index requirements I know of these:
Larger index names: Fixed in DB2 Viper
More columns: Fixed in Viper
Wider index: Fixed in Viper
Index on expression: Under consideration
Allow more than one NULL in Unique indexes: Under consideration
Selective indexes (not necessarily limited to NULL): Under consideration

IP tends to be the least of our problems ;-)

W.r.t. virtual columns we call these "GENERATED BY REFERENCE".
Thing is it's hard to see their usage compared to expressions added to a
VIEW over the base table. Care to elaborate?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 13 '06 #4

Similar topics

2355

script to remove nulls from DB?

by: Steve Walker | last post by:

Hi all. I've been tasked with "speeding up" a mid-sized production system. It is riddled with nulls... "IsNull" all over the procs, etc. Is it worth it to get rid of the nulls and not allow them in the columns anymore? If so, how to go about removing the nulls with a script?

Microsoft SQL Server

2473

Mozilla & IE object model differences?

by: Greg | last post by:

The following code works fine on Mozilla but generates an error* on IE, when the button calling the function is pressed and the slideshow is either on the first slide or last slide. * I can't remember the exact error and I'm on Linux/Mozilla at the moment, but I think it had a problem with the object model?!? The slideshow has a stack of div tags and the buttons are used to change the z-order. Each div contains two other divs: one with a...

Javascript

12532

Getting Avg to really ignore null values

by: manning_news | last post by:

Using SQL2000. According to Books Online, the avg aggregrate function ignores null values. ((3+3+3+3+Null)/5) predictably returns Null. Is there a function to ignore the Null entry, adjust the divisor, and return a value of 3? For example:((3+3+3+3)/4) after ignoring Null entry. If there's more than one null value, then adjust divisor accordingly. For example: ((5+5+5+4+Null+5+5+Null)/8) would be ((5+5+5+4+5+5)/6) after nulls...

Microsoft SQL Server

1575

Setting nulls in Development Center for SQL Functions

by: Rhino | last post by:

I am working with SQL Functions in DB2 for Windows/Linux/UNIX (V8.2.1) and am having a problem setting input parameters for SQL Functions to null in the Development Center. My simple function, called Half, has a single integer input parameter and divides this value by two to produce its result. It also tests the input parameter to see if it is null; if it is null, it returns null. When I click the Run button and try to use the SET TO...

DB2 Database

1767

Allowing Nulls in Code

by: Marcus | last post by:

Wondering if it's possible to allow for Null enteries in SQL/VB code. The example below doesn't work. Am I missing something? Or, is this just not possible? strSQL = "SELECT * FROM tblData " & _ "WHERE " & "tblData.Date = #" & !! & " Or (" & !! Is Null & ")# AND (" & strCriteria & ");"

Microsoft Access / VBA

2965

convert nulls to spaces in file

by: Brian Henry | last post by:

first question... I have a flat file which unfortinuatly has columns seperated by nulls instead of spaces (a higher up company created it this way for us) is there anyway to do a readline with this and not have it affected by the null? because it is right now causes truncated data at wierd places... but as soon as i manually with a hex editor change char(00) to char(20) in the files it reads prerfectly... which leads me to my 2nd...

Visual Basic .NET

2518

Type conversions and nulls

by: Edmund Dengler | last post by:

Howdy all! Just checking on whether this is the expected behaviour. I am transferring data from multiple databases to single one, and I want to ensure that I only have unique rows for some tables. Unfortunately, some of the rows have nulls for various columns, and I want to compare them for exact equality. => create table tmp ( bigint a,

PostgreSQL Database

6518

Inserting nulls in to table conflicting with rule

by: markjerz | last post by:

Hi, I basically have two tables with the same structure. One is an archive of the other (backup). I want to essentially insert the data in to the other. I use: INSERT INTO table ( column, column .... ) SELECT * FROM table2

Microsoft SQL Server

5560

Why would you create an index with "ignore nulls" set to yes?

by: shira | last post by:

I have done a fair bit of searching, but haven't yet been able to find an explanation as to why one would set "ignore nulls" to "yes" when creating an index. I understand what it does (I think), but I'm looking to understand what scenario might prompt either setting (yes or no). Any clarity you can provide is much appreciated! Thanks kindly.

Microsoft Access / VBA

8236

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8679

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8621

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

7159

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6110

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5563

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4174

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2606

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1482

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General